topic Re: Why my Macro code is not responding in SAS Programming

Why my Macro code is not responding

nazmul — Sun, 10 Jun 2018 06:22:10 GMT

Dear Everyone,

I am using the following macro command but without any response from SAS. Could you please tell me why it is happening? I have bunch of text file in separate folders. I want to convert all of them in SAS format. I want to run macro to accomplish the step.

%MACRO POR;
%DO I = 03312011 %TO 12312017;
proc import datafile='H:\Seminar Class\Banking Seminar\Data by me\Bank Call Report\FFIEC data\FFIEC CDR Call Bulk All Schedules &I/FFIEC CDR Call Bulk POR &I.txt'
out=POR&I dbms=dlm replace;
datarow=2; delimiter='09'x;
run;
%END;
%MEND POR;

Log Output:

1052 %MACRO POR;
1053 %DO I = 03312011 %TO 12312017;
1054 proc import datafile='H:\Seminar Class\Banking Seminar\Data by me\Bank Call Report\FFIEC
1054! data\FFIEC CDR Call Bulk All Schedules &I/FFIEC CDR Call Bulk POR &I.txt'
1055 out=POR&I dbms=dlm replace;
1056 datarow=2; delimiter='09'x;
1057 run;
1058 %END;
1059 %MEND POR;

Re: Why my Macro code is not responding

FreelanceReinh — Sun, 10 Jun 2018 10:03:10 GMT

Hi @nazmul,

You submitted the macro definition, but forgot to call the macro. That is, you told SAS to compile the macro, but not to execute the compiled code.

Actually it was good that you omitted the macro call, because otherwise the log would be full of error messages:

As a rule, macro variable references (such as &I) in single quotes are not resolved. So, you should enclose the path in double quotes in order to have the references &I replaced by your date values.
Your %DO loop would iterate 12312017-3312011+1=9000007 times! Not only would it take a long time to execute nine million PROC IMPORT steps, but most of them would most likely result in error messages (even after the above correction) due to non-existing files, e.g. FFIEC CDR Call Bulk POR 3312019.txt. You should think about a way to loop through the relevant dates rather than through nine million integers (without leading zeros) which SAS does not recognize as dates.

Once you've corrected the macro (and have compiled it again), you may want to call it by simply submitting

%POR

Re: Why my Macro code is not responding

ballardw — Mon, 11 Jun 2018 23:26:30 GMT

In addition to @FreelanceReinh's comment you are going to have issues with this line:

proc import datafile='H:\Seminar Class\Banking Seminar\Data by me\Bank Call Report\FFIEC data\FFIEC CDR Call Bulk All Schedules &I/FFIEC CDR Call Bulk POR &I.txt'

For one thing you change from a \ to a / part way through the code.

A second issue occurs twice: you have &I/ and &i.txt. The . following a macro variable tells SAS that the following text is literal or a separate macro variable to resolve. In some cases the missing . will yield an undesired result an potentially an error if the following character could be part of a macro variable name:

222  %let i= 123;
223  %put &i_;
WARNING: Apparent symbolic reference I_ not resolved.
&i_

The second is that since you want a .txt at the end the single . gets lost in the macro concatenation:

226  %let i= 123;
227  %put Wrong: &i.txt;
Wrong: 123txt
228  %put Right: &i..txt;
Right: 123.txt

You have a logic issue that you are very likely to generate HUNDREDS OF THOUSANDS of incorrect filenames.

Since your index values for I are pretty obviously dates then for giggles run this code showing what will happen from 03312011 to 04012011:

%macro dummy;
%DO I = 03312011 %TO 04012011;
%put &i;
%end;
%mend;

%dummy;

And if you examine the results carefully of the above you will notice that the numeric values generate by %DO have no leading zeroes besides creating way too many values.

So you actually want to iterate over date values and then use the text with leading 0 as needed. A data _null_ and Call execute can do this as the macro code for directly manipulating those date values will be ugly:

data _null_;
do i='31MAR2011'd to '31DEC2017'd;
   ic = put(i,mmddyyn8.);
   length longstr $ 200;
   longstr = quote(cat('H:\Seminar Class\Banking Seminar\Data by me\Bank Call Report\FFIEC data\FFIEC CDR Call Bulk All Schedules ',ic,'\FFIEC CDR Call Bulk POR ',ic,'.txt'));
   call execute ('Proc Import datafile=');
   call execute (longstr);
   call execute ('out='||cat('POR',ic)||'dbms=dlm replace;');
   call execute ("datarow=2; delimiter='09'x;run;");
end;
run;