topic Re: Bulk MS Excel Import fails with multiple files in SAS Programming

Bulk MS Excel Import fails with multiple files

metallon — Mon, 04 Apr 2016 15:31:10 GMT

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);

Re: Bulk MS Excel Import fails with multiple files

Reeza — Mon, 04 Apr 2016 15:38:05 GMT

Proc Import guesses 😞

CEm

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

Re: Bulk MS Excel Import fails with multiple files

metallon — Mon, 04 Apr 2016 15:45:58 GMT

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?

Re: Bulk MS Excel Import fails with multiple files

Kurt_Bremser — Tue, 05 Apr 2016 06:44:54 GMT

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

Re: Bulk MS Excel Import fails with multiple files

metallon — Tue, 05 Apr 2016 07:11:00 GMT

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.

Re: Bulk MS Excel Import fails with multiple files

Kurt_Bremser — Tue, 05 Apr 2016 07:18:35 GMT

@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 😉

Re: Bulk MS Excel Import fails with multiple files

metallon — Tue, 05 Apr 2016 07:50:15 GMT

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?

Re: Bulk MS Excel Import fails with multiple files

Kurt_Bremser — Tue, 05 Apr 2016 07:55:30 GMT

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

Re: Bulk MS Excel Import fails with multiple files

metallon — Tue, 05 Apr 2016 10:22:04 GMT

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.

Re: Bulk MS Excel Import fails with multiple files

Kurt_Bremser — Tue, 05 Apr 2016 11:11:28 GMT

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.

Re: Bulk MS Excel Import fails with multiple files

metallon — Tue, 05 Apr 2016 12:52:31 GMT

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

Re: Bulk MS Excel Import fails with multiple files

Kurt_Bremser — Tue, 05 Apr 2016 13:15:41 GMT

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.

Re: Bulk MS Excel Import fails with multiple files

metallon — Wed, 06 Apr 2016 07:44:52 GMT

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

Thanks

Re: Bulk MS Excel Import fails with multiple files

Reeza — Wed, 06 Apr 2016 11:44:31 GMT

16 files is small 🙂