I am using the following code to import multiple excel files to the server. When using the combine files macro I receive an error because on some of the files a column is imported as 'numeric' and in some files it is imported as a character. How can I manipulate multiple files to have the same format? /* Get a list of all Excel files in the work directory */ options validmemname=extend nosymbolgen mprint; filename folder "&mywork."; data FilesInFolder; length Line 8 File $300; List = dopen('folder'); do Line = 1 to dnum(List); File = trim(dread(List,Line)); if find(File, 'xlsx') ge 1 then output; end; drop List Line; run; filename folder clear; /* Create a File# and Name# global macro variable for each excel file (File1, File2, Name1, Name2, etc) */ 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)-5))))); if final then call symputx(trim('Total'), _N_); run; /* Import each Excel file to a SAS dataset (one dataset per Excel file) */ %macro ImportAllSheets; %do i = 1 %to &Total.; proc import out=work.&&name&i. datafile="&mywork.\&&File&i" dbms=xlsx replace; getnames=yes; datarow=2; run; %end; %mend ImportAllSheets; %ImportAllSheets; %macro CombineDatasets; /* Combine all datasets into a single dataset */ data work.final; set %do i = 1 %to &Total.; work.&&name&i. %end; ; run; /* Remove the temporary datasets created by the code above */ proc datasets lib=work nolist; delete FilesInFolder %do i = 1 %to &Total.; &&name&i. %end; ; run; %mend CombineDatasets; %CombineDatasets;
... View more
