BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

View solution in original post

9 REPLIES 9
user24feb
Barite | Level 11

I think you need options:

ToolsOptions 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 SaveOK.

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

ballardw
Super User

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
Super User

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

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
andreas_zaras
Pyrite | Level 9

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
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
ballardw
Super User

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
Pyrite | Level 9

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
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
andreas_zaras
Pyrite | Level 9

Hello Chris,

Thanks for your answer.

Andreas

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 8032 views
  • 1 like
  • 5 in conversation