December 5, 2009

Exporting from MS Excel into SAS

A nifty libname engine named "excel" means no more messy PROC IMPORT for me.

In our research work, we often receive lab data in Microsoft Excel. I used to use PROC IMPORT to import excel data into SAS. However, last year I learned that SAS 9.1 has a great way to quickly access all the excel worksheets in a workbook and then merge them with your master SAS database.

Example 1, the basics:

libname IQdata excel 'C:\CAREStudy\Data\cognitivefunction.xls';
data work.newtable;
  set IQdata.'Sheet1$'n; *excel worksheet name is 'Sheet 1' in this case;
run;
libname IQdata clear;
*until libname is cleared, you won't be able to re-open your excel data file;



Example 2, where excel workbook has three sheets, one for each city:

libname IQdata excel 'C:\CAREStudy\Data\cognitivefunction.xls';
data work.mergedtable;
   set IQdata.'Boston$'n
       IQdata.'Providence$'n
       IQdata.'Baltimore$'n;
run;
libname IQdata clear;


This is just another way to move data from MS Excel to SAS.

No comments: