Resources

Missing observations from Excel to GAUSS

0

Dear all,

I have missing observations in my database and they figure as “.” in my file.xlsx. However, when I read my file with GAUSS, these missing observations are not correctly detected. I have tried 

msym .;

but it doesn’t accomplish what I want.

Thank you in advance for your help.

Tags: asked February 8, 2013

1 Answer

0

If your Excel file has literal dots “.” in it, then GAUSS is reading them in as a character matrix element. To explain this (and verify that this is what is happening) let’s work through a simple example.

//Create a vector with dot characters in it
x = { 1, 2, 3, ".", 5, ".", 7 };
print x;

This code snippet above will create a 1×7 matrix in which the fourth and sixth elements are equal to the character “.”. The print statement will produce this output:

       1.00 
       2.00 
       3.00 
       +DEN 
       5.00 
       +DEN 
       7.00

The +DEN is not a specific number, it means that the element is a denormal. But this particular denormal does have a specific value, it is just not printed out.

You can see that these elements are characters, by using the dollar sign “$” modifier with your print statement, like this:

//Print as a character matrix
print $x;

This print statement will return just the elements that are characters like this:

                 
                 
                 
               . 
                 
               . 

Now you can convert these values from a character “.” to a GAUSS missing value with the miss command.

//Convert all values in 'x' that are equal to a character dot
//to be equal to a missing value and assign this into 'newx'
newx = miss(x, ".");
print newx;
       1.00 
       2.00 
       3.00 
          . 
       5.00 
          . 
       7.00 

At this point the missing values from the Excel file are now missing values in your GAUSS matrix. The msym command does not create missing values, it just allows you to control what GAUSS will display when printing a missing value. For example, if you wanted GAUSS to print MISS instead of the dot, you could accomplish that like this:

msym MISS;
print newx;

will return:

       1.00 
       2.00 
       3.00 
       MISS 
       5.00 
       MISS 
       7.00

The msym command did not change the missing value, it only changed what GAUSS printed out when it encountered the missing value inside the matrix ‘newx’.