Category Archives: SAS Tips

Using Automatic Macro Variable &SYSDATE

In this example, I want to import some data from an Oracle database and save as a SAS data set with today’s date in the filename. To complicate things a little further, I want the date in mmddyy format so that my file name looks like this: claims_research_101113.

The SAS automatic macro variable SYSDATE stores today’s date in DATE7 format, which looks like this: 11OCT13. There is also a SYSDATE9 variable which stores the date in DATE9 format: 11OCT2013. If I want to format the date the way I want it, I first have to use the INPUTN function to specify a numeric informat, and then I can use the %SYSFUNC macro function to format it as MMDDYYn6.

When I have today’s date formatted the way I want it, I store it in the macro variable &date, which I then add on to my filename when I create the table, so that the previous file doesn’t get overwritten. There’s no need to go into the folder and rename the file manually!  That’s what macro variables are for.  Here’s what it looks like:

%let date=%sysfunc(inputn(&sysdate,date9.),mmddyyn6.);
proc sql ;
    create table mbr_data.claims_research_&date as
        select *
            from ccdr.claims_research;
quit;

 

Importing Medical Expenditure Panel Survey Data Into SAS

I did an in-house SAS user group presentation last week on using SAS survey procedures to analyze Medical Expenditure Panel Survey (MEPS) data with regard to insurance coverage in the context of healthcare reform (ACA and the New Individual Segment: Profiling the Uninsured and Non-Group Insured Populations with MEPS 2010 and SAS Survey Procedures). The MEPS 2011 consolidated data file is available as of September 2013 for download. It contains detailed information (over 1900 variables) on demographics, household income, employment, diagnosed health conditions, additional health status issues, medical expenditures and utilization, satisfaction with and access to care, and insurance coverage of those surveyed.

There are several government data sets made available to the public each year that are designed for easy analysis with SAS and other statistical programming software (including STATA and SPSS). I attended a BASUG training by Paul Gorrell back in 2012 which introduced me to some of these data sets. The MEPS website includes programming statements to help you import the data to SAS. If you have a SAS/STAT license with Base SAS of version 9 or above, you have access to four SAS survey procedures (PROC SURVEYFREQ, PROC SURVEYMEANS, PROC SURVEYREG, PROC SURVEYLOGISTIC) that you can use to analyze data from complex survey designs such as MEPS.

You can get started in just a few easy steps. There are a couple of ways to do this, but this is the method I used:
1. Download and run the h147ssp.exe file to extract the data to your chosen library.
2. After you execute the file above, you should be able to find the sas transport data file (h147.ssp) in your folder. Now you have to tell SAS where to find it with a FILENAME statement.
3. Assign the LIBNAME where you want your SAS data set to be created.
4. Import the data using PROC XCOPY.

Here’s an example:
LIBNAME MYLIB ‘C:\Users\C31497\Desktop’;
FILENAME H147 ‘C:\Users\C31497\Desktop\h147.ssp’;
PROC XCOPY IN=H147 OUT=MYLIB IMPORT;
RUN;

That’s it! Next you can run a PROC CONTENTS to get a full variable listing, or you can view the online codebook on the MEPS site. You can find out more about SAS Survey procedures in my NESUG 2013 paper, Proc SurveyCorr.

Two Interview Questions for SAS Programmers

I have a colleague who asks the same two questions every time he interviews a SAS programming consultant for an opening on his team. His first question has to do with the program data vector (pdv). His logic is that understanding the pdv is central to understanding how SAS processes data. Since he is a data step programmer, I also suspect that he uses this question to weed out programmers who rely extensively on PROC SQL, but lack a basic understanding of how the DATA STEP works. I am certainly guilty of preferring PROC SQL over DATA STEP programming, but I do know about the pdv and agree that it is important to understand how SAS processes data in order to make sure your programs run efficiently. Unlike querying with SQL in a relational database, where you want to use a set-based, non-sequential approach to maximize efficiency, SAS processes data sequentially. Understanding this procedural/iterative processing vs. the familiar set-based approach is important for someone coming from SQL to SAS.

When SAS compiles a data step, it creates the program data vector, which contains the automatic variables _N_ and _ERROR_. These variables are not written to the output data set, but they can be accessed and output to another variable or to the log; _N_ stores the number of times the data step has iterated during execution, while _ERROR_ is a binary variable set to 0 unless an error occurs during execution, in which case it is set to 1. As a data step executes and reads the input file, the current observation is read to the pdv, _N_ increments by 1, and the observation gets written to the output file. Then the program loops back to the top of the data step, and the process iterates until it reaches the end of the file, or until an error occurs. You can use this knowledge of the data step and the automatic variables to debug your programs — for example, you can write an observation containing an error to the log using the following statement:

if _error_=1 then put _infile_;

You can also use _N_ as an observation counter, if you are only reading in one observation for each iteration of the data step (it is important to remember that _N_ actually represents the number of iterations of the data step, which will often equal the number of observations read, but not always).

The second question for the prospective SAS programmer is to see if they can name three system options that are useful for debugging code containing macros. These are MPRINT, MLOGIC, and SYMBOLGEN. SYMBOLGEN automatically resolves macro variables used in submitted statements and prints them to the log; this helps you see at a glance if your macro variables are resolving to the values you expect. MPRINT outputs the full macro statements executed when you call a macro and prints them to the log. MLOGIC helps trace the execution of a macro by writing messages to the log at various stages of execution, so you can see more easily where a macro fails.

How to Use ODS to Zip Files in SAS 9.2

Starting with SAS version 9.2, you can use ODS to create zip files in SAS without having to use platform-specific commands (such as Unix commands if your files are on a Unix server). I used ODS to zip a file on my C drive in four steps:

  1. First, I told SAS that I wanted to create a new package. You don’t need to name your package unless you are going to create multiple packages:
    ods package open nopf;
  2. Next, I added my file to the package. You can add multiple files, but I recommend that you only zip a single file at a time. Also note that unless you add the file extension to the full path, your SAS program will run with no errors, but the resulting zip file will be empty:
    ods package add file=’C:\Users\C31497\Desktop\projects\h138.sas7bdat’;
  3. Then I published the zip file and gave it a name. The zip file appears by default in the same path as the original file, but you can specify a different path using the archive_path argument when you assign the properties. If there is an existing zip file with the same name, it will be overwritten:
    ods package publish archive properties(archive_name=’h138.zip’);
  4. Finally, don’t forget to close the ods destination after you’re finished. You may also wish to drop your original (unzipped) file, since this is not done automatically after the zip file is created:
    ods package close;

Here’s what it looks like when you put it together:

ods package open nopf;
ods package add file=’C:\Users\C31497\Desktop\projects\h138.sas7bdat’;
ods package publish archive properties(archive_name=’h138.zip’);
ods package close;

There is still no way to unzip your files outside of using platform-specific syntax, so hopefully SAS is working on that for a future version.

Creating AutoExec Code in SAS Enterprise Guide 4.3

For some users, metadata is already set up by their system administrator in Enterprise Guide so that libraries are automatically assigned; others are not so lucky. If you get tired of having to copy and paste or re-type your libname statement to manually connect to your libraries in Enterprise Guide all the time, you can create an “ad hoc” library in EG. This makes your life easier since every time you open EG, your ad hoc library is automatically assigned. You can also set system options, macros, or other code to run automatically every time you start the application.

To do this quickly and easily, follow these steps:

  • In SAS EG, go to the toolbar
  • Select Tools >> Options >> SAS Programs (left side of screen)
  • Click the check box for “Submit SAS code when server is connected”
  • Select “Edit” (same line as the check box above)
  • When the Edit box pops up, copy and paste the syntax below to automatically connect to your Oracle library (you must enter your own username, password, and schema):

    • libname mylib oracle user=xxxx orapw=xxxxx schema=xxxx path=pathname.com;
    options sastrace=’,,,d’ sastraceloc=saslog mprint symbolgen fullstimer;

    The next time you open EG, look for your “mylib” in the list of libraries on the left side of the EG screen. The above syntax sets up some system options that you may find useful, but you can modify to add additional libname statements, system options, and any other code that you want to run automatically on startup. See my previous post for more about some of these system options.