Bulk MS Excel Import fails with multiple files

[metallon]

Hi SAS Experts,

I got the following macro to import files and noticed that when I add one or more files that some columns are not properly imported anymore. It is really weird. and I cant explain it.

%macro MultImp(dir=,out=);
%let rc=%str(%'dir %")&dir.%str(\%" /A-D/B/ON%');
filename myfiles pipe %unquote(&rc);
data importedfilesoverview;
length fname $256.;
infile myfiles truncover;
input myfiles $100.;
fname=quote(upcase(cats("&dir",'\',myfiles)));

first2chars=quote(substr(myfiles,1,2)); /* country*/
next5chars=quote(substr(myfiles,7,1));  /* year*/

out="&out";
drop myfiles;
call execute('
  proc import dbms=xls out= _temp
            datafile= '||fname||'; getnames=no; datarow=10;
  run;

  /* new: */
  proc sql;
    create table _temp2 as
      select '||first2chars||' as first2chars, '||next5chars||' as next5chars, *
      from _temp;
  run;

  proc append data=_temp2 base='||out||' force; run;
  proc delete data=_temp _temp2; run;
');
run;
filename myfiles clear;
%mend;

%MultImp(dir=R:\myfiles\,out=Importcont_Import);

[Reeza]

Proc Import guesses 😞

 CEm

Is there something different in those file structures from the other files? Empty columns?

[metallon]

I got it. Its the format. One excel column in one file is date the other file has it as standard text. the content looks the same but SAS, upon import, takes the first files format and does not import the rest anymore. is there a way to force SAS to import as character...all columns?

[Kurt_Bremser · Accepted solution]

---

@metallon wrote:
I got it. Its the format. One excel column in one file is date the other file has it as standard text. the content looks the same but SAS, upon import, takes the first files format and does not import the rest anymore. is there a way to force SAS to import as character...all columns?

---

Yes. Instead of using PROC IMPORT, take the DATA step created by PROC IMPORT (it's in the log) and change the input format for the respective column.

Edit: I also see you try to use .xls as a data transfer format. This is a BAD IDEA, Excel is NOT a suitable data transfer format.

Export your data to something sensible (.CSV!) and import that.

[metallon]

---

KurtBremser wrote:

Edit: I also see you try to use .xls as a data transfer format. This is a BAD IDEA, Excel is NOT a suitable data transfer format.

Export your data to something sensible (.CSV!) and import that.

---

Thanks. I know 🙂 but its 16 clients, it is out of my power to re-negotiate data submission procedures. I just have to deal with it.

The IT Guy.

[Kurt_Bremser]

---

@metallon wrote:

---

KurtBremser wrote:

Edit: I also see you try to use .xls as a data transfer format. This is a BAD IDEA, Excel is NOT a suitable data transfer format.

Export your data to something sensible (.CSV!) and import that.

---

Thanks. I know 🙂 but its 16 clients, it is out of my power to re-negotiate data submission procedures. I just have to deal with it.

The IT Guy.

---

My feelings are with you 😉

[metallon]

Thanks 🙂
I got my first file that has the date column formated so I cant use $CHAR50 and the second excel file has it formatted as standard text so $CHAR50 works. Is there no way to ignore the ms excel formats?

[Kurt_Bremser]

You can always read any data into a temporary character variable (that is subsequently dropped) and then do the conversion using the input() function.

[metallon]

hmm I got one excel file with date format 2015-10-29 and one with 2015.10.29 I cant get them in the same column. If I use informat L YYYYMMDD. the 2015.10.29 wont load and if I use $CHAR100. the 2015-10-29 wont load but he 2015.10.29 works.

[Kurt_Bremser]

Hmm.

data test;
input charstring:$10.;
date_wanted = input(charstring,yymmdd10.);
format date_wanted yymmdd10.;
cards;
2015-10-29
2015.10.29
;
run;

proc print;
run;

gives me this result:

                                                                            date_
                                                  Obs    charstring        wanted

                                                   1     2015-10-29    2015-10-29
                                                   2     2015.10.29    2015-10-29

as yymmdd10. is robust with different separator characters.

[metallon]

Thanks. But I have to use the proc import macro. there are too many files.

[Kurt_Bremser]

Define "too many". You see, here we get several hundred different data file layouts out of the production database and production programs. Each has its own SAS program and job to be run by the central scheduler. Each program is handled with a versioning system and fully documented (infiles, outfiles, changes, test runs, etc.).

As long as you just throw the whole heap of data at PROC IMPORT and hope for the best, you have to live with the results. Computers are dumb.

Get a handle on your data structures and bring order into the whole process. This will mean much work up front, but much less hassle later on.

[metallon]

8-16 files. OK I am convinced. I tell the guys responsible that they need to get the MS Excel formats right 🙂

Thanks

[Reeza]

16 files is small 🙂