11-14-2017 05:08 AM
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)
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???
11-14-2017 05:12 AM
11-14-2017 06:22 AM
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...
11-14-2017 05:15 AM
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.
11-14-2017 05:43 AM
Ah, I believe this goes back to the thread last week.
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.