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;
Use a file format where you do not have to rely on the guessing of the Excel import. Any textual format (ie csv) is much better than Excel, which is totally unsuited for a reliable data transfer.
I can suggest you two options:
1) Convert your .xlsx files into .csv and instead of using PROC IMPORT use DATA STEP to import files.
2) After you imported the files into SAS Datasets convert the datatype before combining them.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.