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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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


 

View solution in original post

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

 

Reeza
Super User

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


 

AHeisey
Calcite | Level 5

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

AHeisey
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

ballardw
Super User

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 4062 views
  • 1 like
  • 4 in conversation