Covert SAS dataset to SPSS with formats

Often I want to include the SAS variable formats when I hand-off a database in SPSS. Our research group sets up all databases in SAS, but some investigators prefer to work in SPSS for analysis. Here are the steps I use:

1. In SAS, save your formats files as a SAS dataset:

libname tango 'G:\argentina';
libname mylib 'G:\library';
proc format library = mylib cntlout=tango.sas_fmts;
run;

2. In SPSS, run this syntax:

GET
  SAS DATA ='G:\argentina\tango_baseline.sas7bdat'
      /FORMAT ='G:\argentina\sas_fmts.sas7bdat'.
  DATASET NAME Tango WINDOW=FRONT.

3. In SPSS, once you see the data in the editor, save the dataset as *.sav

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.