User Defined Missing Values in Enterprise Guide

[andreas_zaras]

Hello,

I want to create a data set in SAS Enterprise Guide with data drawn from a questionnaire. I also want to type in some user defined missing values. I know that in the Table Editor of Base SAS i can insert up to 28 user defined missing values by typing ._ or .a or .b etc until .z. These values would appear in the data set without the periods but they will be treated as missing values. I want to do the same thing in EG by typing the user defined missing values but it does not seem to happen. When i type in e.g. .a in a cell of a numeric variable i get an error that this value is innapropriate.

Thanks in advance,

Andreas

[user24feb]

I think you need options:

Tools►Options from the menu at the top of the application. Select the Custom Code option in the selection pane, and then select both the Insert custom SAS code before task and query code and Insert custom SAS code after task and query code check boxes. Click the Edit button for the "before" code and type options missing = " ";. Click Save. To reset the option for other items that are run, such as code for SAS programs, change the option back. Click the Edit button for the "after" code and type options missing = ".";. Click Save►OK.

http://support.sas.com/resources/papers/proceedings10/145-2010.pdf

[ballardw]

From a certain amout of experience I would recommend creating a separate table with id variables plus the custom values and update the base table using that.

Reason: If you have to go back to a step before the manually enterend data for any reason, or accidentaly run code that creates the data set, then you have to go back and manually enter them again.

AND I would make this update dataset with datastep code if at all practicable instead of entering manually for similar reasons.

I have inherited projects in mid-stream where someone did exactly what you propose. After sending a data set to the client and having the client request some additional information in the data set, code was added and recreated the data set. But the manual step was "forgotten". So I ended up having to placate a customer, try to chase down the manual data and correct the issue. Many non-billable hours wasted.

[Quentin]

Agree with ballardw, typing over data is generally a bad idea.

If you don't want to use custom SAS code, it looks like the Query Builder is happy to recode values into special missing values.

Below paper shows recoding to ., but recoding to .A etc also works.

http://support.sas.com/resources/papers/proceedings14/SAS257-2014.pdf

[andreas_zaras]

Hello,

Thanks for your answer. Let us say that i have a questionnaire with 30 variables (questions) and 400 respondents (observations). I do the data entry in Excel and i have 4 user defined missing values. I use the values 999, 888, 99, 88 for the user defined missing values. I then import the data to enterprise guide and i want to code the 4 user defined missing values with .a,.b, .c, .d. If the missing values are in all the 30 variables this means that i have to create 30 recodings using query builder which is not efficient. Is there any other more effficient way to recode the four user defined missing values to .a,.b,.c, .d without using SAS code, only by using the GUI?

Thanks in advance,

Andreas

[Quentin]

Hi,

I don't know much about the GUI (answering your question prompted my first look at the query builder).  With SAS code, it would be trivial to do the recode.

Here is a crazy thought that may not work.

Try entering the data in Excel with values A B C D.

Then if you can add a little bit of "precode" before the step that reads the excel file, add this statement:

missing A B C D ;

There are many different ways SAS can import an Excel file, and this probably wouldn't work with many of them.  But if you get lucky, you might be able to force SAS to treat these variables as numeric, and it might then honor the special missing values.

I try to avoid reading from Excel as much as possible.  If you know how to read in a text file  (e.g. csv) using the GUI, then this approach is more likely to work there, because you should be able to tell SAS the variables are numeric, rather than have SAS guess.

HTH,

--Q.

[ballardw]

Personally  I would export the data to CSV and use a datastep to read the data. Then those variables can be read with one or more custom formats that would treat those values as needed;

If those are the only values that need recoding and are consistent across variables:

proc format;

invalue MyRecode

999 = .A

888 = .B

99  = .C

88 = .D

;

run;

You can use the wizard in base SAS to generate code to read a CSV file using proc import that you can then edit (look at the log).

For the variables that require the recode replace the likely best8. or best16. in informats with

MyRecode. to use the above format.

I don't use EG so I can't point to exactly how to point-and-grunt through menus to accomplish the above.

NOTE: if some of your variables have valid 88 and 99 responses (quite likely in those that you have 999 and 888 values) then make a separate format for the 999/888 set and another for 99/88.

[andreas_zaras]

Hello,

Thanks for your answer. I was just wondering whether the task can be done in EGuide by just using the GUI (without writing any code).

Thanks again,

Andreas

[ChrisHemedinger · Accepted solution]

Hi Andreas --

I don't think your task can be accomplished through the GUI very easily.  Recoding a single variable in the Query Builder is simple to do, as Quentin points out, but there isn't a way to apply that same operation across all 30 variables without a lot of tedious work.  In this case, a SAS program node would be the most efficient approach.

Chris

[andreas_zaras]

Hello Chris,

Thanks for your answer.

Andreas