Reading in 100+ XLS files

[Walternate]

Hi all,

I have a set of XLS (not a typo) files that need to be read in to SAS.

I'm using libname Excel, which is working pretty well except for one thing:

I want every one of the 150+ variables to be read in as character, but SAS is deciding basically all of them are numeric (I don't know if that's because the first few rows are missing? But it is).

I tried adding an attrib statement when reading in the files, but this just causes a bunch of warnings that each var is being defined as both char and numeric. 

This is the relevant  code snippet, which cycles through a file list (&list_of_files), grabs each sheet name, and reads in the file using the attrib statement: 

%let i = 1;

%do %while (%scan(&list_of_files., &i., ^)> ' ');

%let file = %scan(&list_of_files., &i., ^);

libname xlibr excel "&dir.\&file.";

/*GET SHEET NAME FOR FILE FIRST*/

proc sql noprint;

select memname into :sheets separated by '^' 

from sashelp.vtable

where libname = "XLIBR";

quit;

/*READ FILE IN FULLY*/

%macro readem;

%do j = 1 %to 1;

%let sheet=%scan(&sheets., &j., ^);

data imported_file_&letter._&i.;

attrib 

"ID"n length= $10                                                                               

"pid"n length= $30                  

ETC .for all vars

;

set xlibr."&sheet."n (firstobs=4);

run;

The firstobs is because the data files have weird empty header rows at the top. 

Any help is much appreciated!

%end;

[ballardw]

Are all of these "hundreds" of XLS supposed to be of the same structure?

If they are of the same structure it may be worth writing a data step that takes, a hopefully known, sheet name and does the conversion manually. Which for numeric to character is going to mean multiple: Newvarname = put(numericvarname, someformatname. -L) statements. The Attrib would go to the new variables with different names. And drop the old ones.

Or do the rename shuffle as in this thread: https://communities.sas.com/t5/SAS-Programming/Why-I-couldn-t-change-categories-of-some-of-my-charac...

Make sure to set the length of the resulting variables so you know what they are when you go to use these.

PS: You do not want DATES, TIMES or DATETIME values as character. Almost any use of those would require a first step of getting a numeric date, time or datetime value. So don't cause yourself more work.

[Tom]

Does each XLS file contain multiple sheets?

One trick that usually forces the variables to character is to tell it NOT to use the first row as the variable names.

Not sure about EXCEL engine but for normal PROC IMPORT code that is the GETNAMES=NO; statement.

[Reeza]

If they're all the same structure and somewhat well structured, I'd go through the effort to export the data to CSV for each file and process that instead. VBS to do the conversion.

https://gist.github.com/statgeek/878e585102c14e01581f55dbe972d27e

May need to be modified for multiple sheets, Been a few years since I used this and no longer on a Windows system to test.

[Ksharp · Accepted solution]

Did you try MIXED option? Like:
libname xlibr excel "&dir.\&file." mixed=yes ;

also could try XLS engine:
libname xlibr xls "&dir.\&file." mixed=yes ;