add variable after importing but before append

0
votes

I'm using a macro found online to import multiple excel files into single sas dataset. And it works very well. But I also want to add one variable "plate" to specify each excel file. Here is the macro:

%macro MultImp(dir=,range=,out=,n=);

%let rc=%str(%'dir %")&dir.%str(\%" /A-D/B/ON%');
filename myfiles pipe %unquote(&rc);

data list;
length fname $256.;
infile myfiles truncover;
input myfiles $100.;

fname=quote(upcase(cats("&dir",'\',myfiles)));
out="&out";
drop myfiles;
call execute('
%do i=1 %to &n.;
proc import dbms=xlsx out= _test
        datafile= '||fname||' replace ;

        range="&range";


run;

data _test;
set _test;
plate=&i;
run;

proc append data=_test base='||out||' force; run;
proc delete data=_test; run;
%end;
');
run;
filename myfiles clear;

%mend;

%MultImp(dir=U:\test,range=summary$.D10:Y200,out=test,n=30);

I wrote a do loop inside of call execute, and run a small data step after proc import, but before proc append. but it doesn't work. Can anyone have any idea how can I add this variable before appending datasets. Thank you very much in advance.

2
excelimportsasproc

2 Answers

0
votes

I think you are just working too hard. Build up the commands into a variable first and then pass it to CALL EXECUTE().

I cannot figure out what the N= parameter was doing so I just removed it.

%macro MultImp(dir=,range=,out=);
data list;
  infile "dir ""&dir"" /A-D/B/ON" pipe truncover ;
  input filename $255.;
  out="&out";
  plate+1;
  length fname $600 cmd $3000;
  fname=quote(catx('\',"&dir",filename));
  cmd=catx(' ','proc import dbms=xlsx out=_test replace'
              ,'datafile=',fname
              ,'; range="&range"; run;'
              ,'data _test; plate=',plate,'; set _test; run;'
              ,'proc append data=_test base=&out force; run;'
              ,'proc delete data=_test; run;'
          )
  ;
  call execute(trim(cmd));
run;
%mend multimp;

proc delete data=test; run;
%MultImp(dir=U:\test,range=summary$.D10:Y200,out=test);
0
votes

What are the pipes (|) doing in your proc append? Most likely you just need to delete the base data before doing this, since your code looks mostly correct. I'm just not sure what the pipes are for or supposed to be doing? Double pipes usually mean concatenation, ie in your call execute they make sense. But not in proc append.

Delete out dataset before macro calls

proc delete data=out;run;

Modify proc append

proc append data=_test base=&out force; run;