SAS 9.4. I want to import multiple Excel files (approx 20 daily files) into SAS and merge them into 1 SAS file. I want to do this using macro. My issue arises when SAS starts to import the first file with no data because some files are empty. It converts my numeric variables into character. I have tried to modify my variables into both numeric/character with no luck. I have attached example data. I get below error msg: NOTE: The import data set has 3 observations and 19 variables. NOTE: DER.20180301. data set was successfully created. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.14 seconds cpu time 0.03 seconds NOTE: Table DER.'20180301.'n has been modified, with 19 columns. NOTE: PROCEDURE SQL used (Total process time): real time 0.08 seconds cpu time 0.00 seconds NOTE: The import data set has 0 observations and 19 variables. NOTE: DER.20180302. data set was successfully created. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.18 seconds cpu time 0.06 seconds ERROR: Character column ID_2 requires a character format specification. ERROR: Character column ID_2 requires a character informat specification. NOTE: Table DER.'20180302.'n has been modified, with 19 columns. NOTE: PROCEDURE SQL used (Total process time): real time 0.11 seconds cpu time 0.00 seconds ERROR: Value 3 on the SELECT clause does not match the data type of the corresponding column listed after the INSERT table name. ERROR: Value 9 on the SELECT clause does not match the data type of the corresponding column listed after the INSERT table name. ERROR: Value 11 on the SELECT clause does not match the data type of the corresponding column listed after the INSERT table name. My code is: ods html newfile = proc; %Let Year = %sysfunc(date(),year4.); %Let LastMonth = %sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),-1, same)),monname.)); /* Find and import relevant excel workbooks into SAS */ libname DER "I:\Dat\DER"; %let path = I:\Dat\3950MFI; filename folder "&path\Control\&Year\&LastMonth"; options validmemname=extend; /* to allow non-standard dataset names */ /* Making a list of all excel workbooks into table FilesInFolder */ data FilesInFolder; length File $300; List = dopen('folder'); do Line = 1 to dnum(List); File = trim(dread(List,Line)); output; end; run; /* Ascending order for varibale File in table FilesInFolder */ proc sort data = FilesInFolder; by File; run; /* Creating global macro variables */ /* not local */ data _NULL_; set FilesInFolder end=final; call symput(cats('File', _N_), trim(File)); call symput(cats('Name', _N_), trim(nliteral(substr(File,1,min(32, length(File)-4))))); if final then call symputx(trim('Total'), _N_); run; %macro loop; /* Imports all workbooks specified in variable List from table FilesInFolder and saves them as datasets in Library DER */ %do i = 1 %to &Total; proc import datafile="&path\Control\&Year\&LastMonth\&&File&i" out=DER.&&Name&i DBMS=xlsx REPLACE; sheet=DER; getnames=yes; run; proc sql; alter table DER.&&Name&i modify COMMENT char(60) format=$60. informat=$60. modify ID_2 char(60) format=$60. informat=$60. modify ID_3 char(60) format=$60. informat=$60. modify PRE_THEM_ID (60) format=$60. informat=$60.; /* Inserts all datasets from above into the first dataset from the Library */ %if &i gt 1 %then %do; proc sql; insert into DER.&Name1 select * from DER.&&Name&i; quit; %end; %end; proc export data=DER.&Name1 outfile= "I:\Dat\3950MFI\OUT\DER_&LastMonth..xlsx" DBMS=xlsx REPLACE; sheet=DER; run; %mend loop; %loop
... View more