DATA Step, Macro, Functions and more

Bulk MS Excel Import fails with multiple files

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 133
Accepted Solution

Bulk MS Excel Import fails with multiple files

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


Accepted Solutions
Solution
‎04-05-2016 03:11 AM
Super User
Posts: 7,854

Re: Bulk MS Excel Import fails with multiple files

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 19,861

Re: Bulk MS Excel Import fails with multiple files

Proc Import guesses Smiley Sad

 CEm

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

Frequent Contributor
Posts: 133

Re: Bulk MS Excel Import fails with multiple files

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?
Solution
‎04-05-2016 03:11 AM
Super User
Posts: 7,854

Re: Bulk MS Excel Import fails with multiple files

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 133

Re: Bulk MS Excel Import fails with multiple files

Posted in reply to KurtBremser

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

Super User
Posts: 7,854

Re: Bulk MS Excel Import fails with multiple files


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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 133

Re: Bulk MS Excel Import fails with multiple files

Posted in reply to KurtBremser
Thanks Smiley Happy
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?
Super User
Posts: 7,854

Re: Bulk MS Excel Import fails with multiple files

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 133

Re: Bulk MS Excel Import fails with multiple files

Posted in reply to KurtBremser
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.
Super User
Posts: 7,854

Re: Bulk MS Excel Import fails with multiple files

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 133

Re: Bulk MS Excel Import fails with multiple files

Posted in reply to KurtBremser
Thanks. But I have to use the proc import macro. there are too many files.
Super User
Posts: 7,854

Re: Bulk MS Excel Import fails with multiple files

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 133

Re: Bulk MS Excel Import fails with multiple files

Posted in reply to KurtBremser

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

Thanks

Super User
Posts: 19,861

Re: Bulk MS Excel Import fails with multiple files

16 files is small Smiley Happy 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 393 views
  • 2 likes
  • 3 in conversation