I have 35 excel workbooks with 22 sheets in each that I am trying to import into SAS 9.4 and then create a master dataset for each of the 22 sheets. I found this cool code to export an entire excel workbook in one go, and it's great. But I don't want to do it 35 times. I'm not familiar with macros and want to know if I can apply a do loop for this piece of code.
This is the code I'm using to import an entire workbook in one step and it works like a charm. Since all my 35 workbooks have the same sheet names I'm creating separate libraries to send the SAS datasets by sheet into, so that I can have them all available for merging late.
libname MyFiles XLSX '&path.\abc.xlsx';
libname XYZ '&path2.\XYZ';
proc copy in=MyFiles out=XYZ;
run;quit;
libname MyFiles;
/* this is the do loop I tried. Did not see any errors but not producing any results */
%let path = C:\HowToProcCopy;
%let sub_rec = A B C D E;
%let path2 = C:\HowToProcCopy\SAS Datasets;
%let sub_rec2 = M N O P Q;
%macro all;
%do i = &sub_rec;
%do j = &sub_rec2;
libname myfiles xlsx "&path\&i.xlsx";
libname &j "&path2\&j";
proc copy in = myfiles out = &j;
run;
libname myfiles;
%end;
%mend;
%all;
Macro variables do not resolve in single quotes, you need double quotes. Your log sometimes warns you about that.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.