BookmarkSubscribeRSS Feed
don21
Quartz | Level 8

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!

4 REPLIES 4
PeterClemmensen
Tourmaline | Level 20

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.

don21
Quartz | Level 8

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

Kurt_Bremser
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1809 views
  • 2 likes
  • 4 in conversation