I'm trying to import several xlsx files from the same subfolder using macro. Then I would like to join them. I've read several previous posted and losing my mind. I'm new to using macro and want to challenge myself and to expand my SAS knowledge. The long version is as follows (then with proc sql to join the datasets). proc import replace dbms=xlsx datafile='C:\Operations\CT\ Copy of ts_201706_final.xlsx" out=Fer_fin__Jun17; run; proc import replace dbms=xlsx datafile='C:\Operations\CT\Copy of ts_201707_final.xlsx" out=Fer_fin__Jul17; run; proc import replace dbms=xlsx datafile='C:\Operations\CT\ Copy of ts_201708_final.xlsx" out=Fer_fin__Aug17; run; proc import replace dbms=xlsx datafile="C:\Operations\CT\ Copy of ts_201709_final.xlsx" out=Fer_fin__Sep17; run; proc import replace dbms=xlsx datafile='C:\Operations\CT\ Copy of ts_201710_final.xlsx" out=Fer_fin_Oct17; run; I'm trying to follow the steps posted by previous members https://communities.sas.com/t5/General-SAS-Programming/How-to-import-multiple-Excel-file-to-SAS/td-p/151469/page/2 /* 1. Get file lists in the directory*/ filename dirlist pipe 'dir "D:\..." /b'; /* 2. Resolve file names to SAS dataset "files" */ data files; length fname $20; infile dirlist truncover length= reclen; input fname $varying20. reclen; run; /* 3. Store the number of files we want to import into macro variable "nfile" */ data _null_; set files nobs= nobs; call symput('nfile', nobs); stop; run; /* 4. Import all the files we want into SAS dataset*/ %macro fileinput; %local i; %do i= 1 %to &nfile; /* Store import path and output filename into macro variables*/ data _null_; set files(firstobs= &i obs= &i); /* The length of fpath can't be over 201 since windows limitation */ call symput('fpath', "D:\..." || fname); call symput('foutname', scan(fname, 1, '.') ); run; /* Excel file import*/ proc import out= work.&foutname datafile= "&fpath" dbms=excel replace; range="sheet1$"; getnames=yes; mixed=no; scantext=yes; usedate=yes; scantime=yes; run; %end; %mend; %fileinput;
... View more
