topic Re: importing multiple .xls files in to sas in SAS Programming

importing multiple .xls files in to sas

don21 — Tue, 14 Nov 2017 10:08:02 GMT

dear all,

I have a requirement to impot some 42 .xls files into sas atonce.

1. All the files are from same location (let say they are country files).

2. all are .xls files

3. all of them have same variable names (46 variables in each)

Challenges:

1. they doesnt have a variable in them saying "country" (because the file name itself gives the country name)

2. the sample import query did not work when tried to import a single file (Error: "Too many XF records for Excel" and "Requested Input File Is Invalid")

3. if we succeed to import them all at once, there is a problem in identifying the data (like which belongs to which country)

4. there should be column created in the data with country name

Is there any way to create a column "country in each of the file and import them atonce???

Please help!

Re: importing multiple .xls files in to sas

PeterClemmensen — Tue, 14 Nov 2017 10:12:06 GMT

You can use this example to import all files from a directory

http://support.sas.com/kb/41/880.html

Next, I would use the DSNAME Function to assign the data set name to a new variable in each of your data sets.

Re: importing multiple .xls files in to sas

Kurt_Bremser — Tue, 14 Nov 2017 10:15:38 GMT

Can the crappy Excel format, use .csv files. A multiple of CSV files can easily be imported in a single data step using wildcards, and the name of the currently read file can be determined within the step. See the documentation of the infile statement.

With proc import, you have to import each file separately, and then combine the resulting datasets, using a similar technique in the set statement. But be aware that datasets imported with the guessing procedure proc import can (and will) have different attributes, causing WARNINGs or ERRORs and possible data loss when concatenating.

Re: importing multiple .xls files in to sas

RW9 — Tue, 14 Nov 2017 10:43:55 GMT

Ah, I believe this goes back to the thread last week.

https://communities.sas.com/t5/SAS-Enterprise-Guide/Importing-multiple-pwd-protected-xlfiles-from-a-URL-at-the-same/m-p/412243#M26483

You can import all the files in a folder, either by command line or by using dopen() type functions and looping over that:

filename tmp pipe 'dir "c:\tmp\*.xls" /b";

data _null_;
  infile tmp;
  input;
  call execute('proc import datafile="'||strip(_input_)||'" out=want'||strip(put(_n_,best.)||'; run;');
run;

This creates a proc import for each file returned by the dos command dir. You can do the same using dopen:

data _null_;
   drop rc did;
   rc=filename("mydir","physical-name");
   did=dopen("mydir");
   if did > 0 then do;
     do i=1 to did;
       call execute('proc import....');
     end;
   end;
run;

However there is no way of telling up front if:

a) the proc import will work

b) it will give the same result

Excel is a really bad format, proc import is a guessing procedure, put the two together and you have a mess. The safest way is to create a migration plan, convert the data into usable format, then read the usable format based on a given specification - then the process is repeatable and auditable. Also if the files change in the future you have evidence to go back and bill them again to re-work.

Re: importing multiple .xls files in to sas

don21 — Tue, 14 Nov 2017 11:22:50 GMT

Hi Draycut,

I did use the code and the result was that a data set has been created with all the file names in the given location which is great but, I am trying to update a column names cntry cde in each file with its file name and import all those xls files intoto sas atonce... 🙂