topic Re: Macro for reading multiple Excel Files in one Dataset %macro w2sas05 in SAS Data Management

Macro for reading multiple Excel Files in one Dataset %macro w2sas05

AHeisey — Fri, 07 Sep 2018 12:43:56 GMT

Greetings,

NOTE: Using SAS 9.4 and Office 2016

I inherited a macro to combine multiple Excel files into one dataset.

%macro w2sas05(input=P:\MICHC\CHW Data\2018_Q3,out=work.Out);

%let dir=%str(%'dir %")&input.%str(\%" /A-D/B/ON%');

filename myfiles pipe %unquote(&dir);

data list1; length fname $256.;

infile myfiles truncover;

input myfiles $100.;

/* put infile;*/

fname=quote(upcase(cats("&input",'\',myfiles)));

drop myfiles;

run;

filename myfiles clear;

%let j=1;

%let dsid=%sysfunc(open(list1));

%let rc=%sysfunc(fetch(&dsid));

%do %while(&rc=0);

%let file=%sysfunc(getvarc(&dsid,%sysfunc(varnum(&dsid,fname))));

PROC IMPORT DBMS=EXCEL OUT= _&j

DATAFILE= &file REPLACE ;

GETNAMES=YES;

SHEET="Out_WKST";

MIXED=YES;

SCANTEXT=NO;

USEDATE=YES;

SCANTIME=YES;

DBSASLABEL=NONE;

RUN;

data _&j;

set _&j;

if Contractor = '' then delete;

run;

proc append data=_&j base=&out force; run;

proc delete data=_&j; run;

%let rc=%sysfunc(fetch(&dsid));

%let j=%eval(&j+1);

%end;

%let rc=%sysfunc(close(&dsid));

%mend w2sas05;

%w2sas05 (input=P:\MICHC\CHW Data\2018_Q3,out=work.Out);

I run this macro for each worksheet in the Excel file. I recently updated the data booklet, and the macro is not reading the new variables. Do I need to change something in the excel files so the macro will call in all of the data? or do I need to tweak the SAS coding? Thanks in advance for your help!!

-Angela

Re: Macro for reading multiple Excel Files in one Dataset %macro w2sas05

RW9 — Fri, 07 Sep 2018 12:55:21 GMT

There isn't anything in that code which would restrict it as far as I can tell. I would of course not recommend using Excel for data, and not using proc import for importing - the two together are terrible for getting data into any system.

As you are using 9.4 you can simplify it by using the xlsx libname to read/write Excel files:

https://blogs.sas.com/content/sasdummy/2015/05/20/using-libname-xlsx-to-read-and-write-excel-files/

This will simplify your code, but it does not remove the inherent bad data source, or guessing procedure import.

You could then do something like:

filename tmp pipe 'dir "c:/abc/*xlsx" /b';

data _null_;
  infile tmp;
  length temp $200;
  input temp $;
  call execute(cats('libname ex xlsx "c:/abc/',temp,'";'));
  call execute('proc copy in=ex out=work;run;');
  call execute('libname ex clear;');
run;

This will open each file from the directory input, copy all sheets to work, then clear the libname. You can add in any append code, maybe just append everything in work. However as Excel is a bad data source, and you are guessing the data structure, you can be pretty sure that every run your append will fail.

Re: Macro for reading multiple Excel Files in one Dataset %macro w2sas05

Reeza — Fri, 07 Sep 2018 14:56:07 GMT

Is the INPUT parameter correct?

IMO its not a good idea to include the parameter values in the macro definition, unless they're always the same I suppose or default.

But if you moved the files you need to update that path.

%macro w2sas05(input=P:\MICHC\CHW Data\2018_Q3, out=work.Out);

The only limit I'm aware of with Excel is sometimes there are issues when you have over 256 columns. Is that the case here?

@AHeisey wrote:

Greetings,

NOTE: Using SAS 9.4 and Office 2016

I inherited a macro to combine multiple Excel files into one dataset.

%macro w2sas05(input=P:\MICHC\CHW Data\2018_Q3,out=work.Out);

%let dir=%str(%'dir %")&input.%str(\%" /A-D/B/ON%');

filename myfiles pipe %unquote(&dir);

data list1; length fname $256.;

infile myfiles truncover;

input myfiles $100.;

/* put infile;*/

fname=quote(upcase(cats("&input",'\',myfiles)));

drop myfiles;

run;

filename myfiles clear;

%let j=1;

%let dsid=%sysfunc(open(list1));

%let rc=%sysfunc(fetch(&dsid));

%do %while(&rc=0);

%let file=%sysfunc(getvarc(&dsid,%sysfunc(varnum(&dsid,fname))));

PROC IMPORT DBMS=EXCEL OUT= _&j

DATAFILE= &file REPLACE ;

GETNAMES=YES;

SHEET="Out_WKST";

MIXED=YES;

SCANTEXT=NO;

USEDATE=YES;

SCANTIME=YES;

DBSASLABEL=NONE;

RUN;

data _&j;

set _&j;

if Contractor = '' then delete;

run;

proc append data=_&j base=&out force; run;

proc delete data=_&j; run;

%let rc=%sysfunc(fetch(&dsid));

%let j=%eval(&j+1);

%end;

%let rc=%sysfunc(close(&dsid));

%mend w2sas05;

%w2sas05 (input=P:\MICHC\CHW Data\2018_Q3,out=work.Out);

I run this macro for each worksheet in the Excel file. I recently updated the data booklet, and the macro is not reading the new variables. Do I need to change something in the excel files so the macro will call in all of the data? or do I need to tweak the SAS coding? Thanks in advance for your help!!

-Angela

Re: Macro for reading multiple Excel Files in one Dataset %macro w2sas05

AHeisey — Fri, 07 Sep 2018 15:08:25 GMT

Thanks for the replies. After digging deeper, I realized the macro was reading hidden worksheets that needed to be updated with the new variables.

Re: Macro for reading multiple Excel Files in one Dataset %macro w2sas05

ballardw — Fri, 07 Sep 2018 15:24:21 GMT

@AHeisey wrote:

I run this macro for each worksheet in the Excel file. I recently updated the data booklet, and the macro is not reading the new variables. Do I need to change something in the excel files so the macro will call in all of the data? or do I need to tweak the SAS coding? Thanks in advance for your help!!

-Angela

By "new variables" do you mean that some of the sheets have different column headings/ numbers of columns of populated data?

Proc APPEND will not add additional variables to the "base" data set from a "data" data set. You would have to use a data step with two or more data sets on the SET statement or some potentially very ugly proc sql code.

Re: Macro for reading multiple Excel Files in one Dataset %macro w2sas05

AHeisey — Thu, 13 Sep 2018 21:32:17 GMT

The only limit I'm aware of with Excel is sometimes there are issues when you have over 256 columns. Is that the case here?

Hi,

This was not the original issue. But this is another issue. Is there a way to tell SAS to read more columns?

Thanks in advance for your help

Angela

Re: Macro for reading multiple Excel Files in one Dataset %macro w2sas05

RW9 — Fri, 14 Sep 2018 08:12:30 GMT

Use a modern engine like the one I presented in my post, older engines have limitations based on the limitations of the software at that point. Excel is a really bad format at the best of times, but using an old format of it just compounds the problem, as does using proc import to read it.

Bad data source + guessing procedure = garbage out.

Maxims 3, 11, 14, 22

https://communities.sas.com/t5/SAS-Communities-Library/Maxims-of-Maximally-Efficient-SAS-Programmers/ta-p/352068