BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
metallon
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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

View solution in original post

13 REPLIES 13
Reeza
Super User

Proc Import guesses 😞

 CEm

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

metallon
Pyrite | Level 9
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
Super User

@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
Pyrite | Level 9

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
Super User

@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
Pyrite | Level 9
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?
metallon
Pyrite | Level 9
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
Super User

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
Pyrite | Level 9
Thanks. But I have to use the proc import macro. there are too many files.
Kurt_Bremser
Super User

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
Pyrite | Level 9

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

Thanks

Reeza
Super User

16 files is small 🙂 

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
  • 13 replies
  • 1357 views
  • 2 likes
  • 3 in conversation