importing multiple .xls files in to sas

Reply
Contributor
Posts: 35

importing multiple .xls files in to sas

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!

PROC Star
Posts: 851

Re: importing multiple .xls files in to sas

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.

Contributor
Posts: 35

Re: importing multiple .xls files in to sas

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... Smiley Happy

Super User
Posts: 8,054

Re: importing multiple .xls files in to sas

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 8,174

Re: importing multiple .xls files in to sas

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-...

 

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.

Ask a Question
Discussion stats
  • 4 replies
  • 101 views
  • 2 likes
  • 4 in conversation