Import Multiple Excel Files where Columns have different formats

[wadar]

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;

[Kurt_Bremser]

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.

[SuryaKiran]

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.