My SAS - SAS Code

Back to SAS Code

Example to transfer MS-excel data into SAS data

SAS Code:

dm 'log; clear; out; clear;';
/*
          Name: example_excal2sas.sas

   Description: Example to transfer MS-excel data into SAS data

   Notes:
                Use proc import and data step to import data from excel spreadsheet

         History:   Date        Init Comments
                    01/10/2006 SZ    created

*/

%put %sysfunc(date(),date9) %sysfunc(time(),hhmm);

options linesize=175 pagesize=52 pageno=1;

%let path = c:\test;

%let path_excel = &path.\doc\corr.xls;

/* Use import to read excel sile */
PROC IMPORT OUT = _temp1
          DATAFILE = "&path_excel."
          DBMS     = EXCEL REPLACE;
          SHEET    = "path_info";
          GETNAMES = yes;
          SCANTIME = NO;
          MIXED    = yes;
          SCANTEXT = YES;
          USEDATE = YES;
          TEXTSIZE = 50;
RUN;

proc print data = _temp1;
   title "Using impot";
run;

/* Use libname and data step to read excel file */
libname xls1 "&path_excel.";
data temp_1;
set xls1."path_info$"n;
run;

proc print data = temp_1;
   title "Using data step";
run;

/* Save data into macro variables */
proc sql noprint;
   create table _temp2 as
   select form, root_path, directory, form_name, key_variables, sheet_name, num_key, date
   from _temp1
   where compress(form) ne '';

   %let form_num = &sqlobs;
   %put sqlobs = &sqlobs;

   select form, root_path, directory, form_name, key_variables, sheet_name, num_key, date
   into :form_1       - :form_&form_num.,
        :root_path_1 - :root_path_&form_num.,
        :directory_1 - :directory_&form_num.,
        :form_name_1 - :form_name_&form_num.,
        :key_var_1    - :key_var_&form_num.,
        :sheet_name_1 - :sheet_name_&form_num.,
        :num_key_1    - :num_key_&form_num.,
        :date_1       - :date_&form_num.

   from _temp2;

quit;

%macro aaa;
   %do i = 1 %to &form_num.;
      %put &&&form_&i.. <<>> &&&root_path_&i.. <<>> &&&directory_&i.. <<>> &&&key_var_&i.. <<>> &&&sheet_name_&i..;
      %put     ---->> &&&form_name_&i.. <<>> &&&num_key_&i.. <<>> &&&date_&i..;
   %end;
%mend aaa;
%aaa;

Above code includes two methods to read excel spreadsheet. You can also use proc access, proc dbload or ods targets.excel file="excel file name" to import or export data.

To use this code, you need to save MS-excel file corr.xls to the directory of c:\test\doc or change the code "%let path_excel = &path.\doc\corr.xls;" to make macro variable &path_excel point the directory where corr.xls saved.

If you have any questions, feel free to send me feedback.