Greetings, NOTE: Using SAS 9.4 and Office 2016 I inherited a macro to combine multiple Excel files into one dataset. %macro w2sas05(input=P:\MICHC\CHW Data\2018_Q3,out=work.Out); %let dir=%str(%'dir %")&input.%str(\%" /A-D/B/ON%'); filename myfiles pipe %unquote(&dir); data list1; length fname $256.; infile myfiles truncover; input myfiles $100.; /* put infile;*/ fname=quote(upcase(cats("&input",'\',myfiles))); drop myfiles; run; filename myfiles clear; %let j=1; %let dsid=%sysfunc(open(list1)); %let rc=%sysfunc(fetch(&dsid)); %do %while(&rc=0); %let file=%sysfunc(getvarc(&dsid,%sysfunc(varnum(&dsid,fname)))); PROC IMPORT DBMS=EXCEL OUT= _&j DATAFILE= &file REPLACE ; GETNAMES=YES; SHEET="Out_WKST"; MIXED=YES; SCANTEXT=NO; USEDATE=YES; SCANTIME=YES; DBSASLABEL=NONE; RUN; data _&j; set _&j; if Contractor = '' then delete; run; proc append data=_&j base=&out force; run; proc delete data=_&j; run; %let rc=%sysfunc(fetch(&dsid)); %let j=%eval(&j+1); %end; %let rc=%sysfunc(close(&dsid)); %mend w2sas05; %w2sas05 (input=P:\MICHC\CHW Data\2018_Q3,out=work.Out); I run this macro for each worksheet in the Excel file. I recently updated the data booklet, and the macro is not reading the new variables. Do I need to change something in the excel files so the macro will call in all of the data? or do I need to tweak the SAS coding? Thanks in advance for your help!! -Angela
... View more