How do I Run a Macro in do loop

[JohanK]

Hello, I am not a very senior programmer and I try to run a SAS code, using a macro within a Do While Loop. Here are bits of my code: 

PROC SQL;
SELECT CEIL(MAX(VISNUM)) INTO :MAXVISNUM 
FROM LAB6;
QUIT;

%let CNUM=1; 
%let VNUM=1; 

%macro LAB7;
DATA LAB7;
Length VISIT $18;
SET COHORT&CNUM.;
IF VISNUM NE &VNUM. THEN DELETE;
DROP LBORRESU LBORRES UNL ;
RUN;
%mend LAB7;

data MYDATA ;
VINUM=1;
do until (VINUM=&MAXVISNUM.);
%LAB7;
VINUM=VINUM+1;
end;
run;

it gives me these messages in the log: 

340 do until (VINUM=&MAXVISNUM.);
341 %LAB7;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.MYDATA may be incomplete. When this step was stopped
there were 0 observations and 1 variables.
WARNING: Data set WORK.MYDATA was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

NOTE: Line generated by the invoked macro "LAB7".
1 DATA LAB7; Length VISIT $18; SET COHORT&CNUM.; IF VISNUM
-
---
117
161
1 ! NE &VNUM. THEN DELETE; END; DROP LBORRESU LBORRES UNL ; RUN;

ERROR 117-185: There was 1 unclosed DO block.

ERROR 161-185: No matching DO/SELECT statement.

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.LAB7 may be incomplete. When this step was stopped
there were 0 observations and 6 variables.
WARNING: Data set WORK.LAB7 was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds

342 *%TRANSP;
343 *%WIDE;
344 VINUM+1;
-----
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

345 end;
---
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

346 proc print;
347 run;

Can someone help me out? I do not know what I am doing wrong

[PaigeMiller]

Macros produce code and that code replaces the macro call. The resulting code must be legal valid working SAS code.

When you run your program that has %LAB7, the code that actually runs is this:

PROC SQL;
SELECT CEIL(MAX(VISNUM)) INTO :MAXVISNUM 
FROM LAB6;
QUIT;
%let CNUM=1; 
%let VNUM=1; 

data MYDATA ;
    VINUM=1;
    do until (VINUM=&MAXVISNUM.);
        /* Your %LAB7 is replaced by the contents of the macro */
        DATA LAB7;
            Length VISIT $18;
            SET COHORT&CNUM.;
            IF VISNUM NE &VNUM. THEN DELETE;
            DROP LBORRESU LBORRES UNL ;
        RUN; /* End of %LAB7 */ ;
      VINUM=VINUM+1;
    end;
run;

So you now have a DATA step inside a loop inside a DATA step. Is this legal SAS code? No, you can't have DATA steps inside of DATA steps.

When you try to write macros, very good advice is first to create working legal valid SAS code that does what you want for two cases, hard-coded, without macro variable and without macros. Therefore, we request that you follow this advice, produce this working SAS code without macros and without macro variables for two cases, hard-coded, and show that to us, which will then most likely be easy to turn into a macro, and we can help you at that point.

[JohanK]

Thank you so much Paige. Let me explain what I want to achieve...

I produce graphs with GPLOT at the end of the code from Labdata and in total there are over 25 variations of combinations of 2 variables which are needed as input: VISNUM and USUBJID. In order to avoid hardcoding and rerunning the program 25 times I want to use macros, also to prevent errors with hardcoding and a very long code. What I posted is one loop with one macro but in fact I need 2 loops ; a big one with a loop for VISNUM (within this loop 3 macros instead of 1) AND within that loop a smaller one with USUBJID. At the end I want to get 25 graphs.. 

I have written code which works with hardcoding, just simple code. Now I would like to convert that code to code inclusive macros. Sorry that I cannot share more info ..

[PaigeMiller]

I have written code which works with hardcoding, just simple code. Now I would like to convert that code to code inclusive macros. Sorry that I cannot share more info ..

SHOW US the code.

[Tom]

I do not understand what you are trying to LOOP over.

You definitely cannot execute a data step inside the middle of another data step.  So you logic cannot work (with or without using a macro to generate some of the code).

Please explain what you are trying to do.  (not HOW, but WHAT).

[JohanK]

Thank you so much Tom. Let me explain what I want to achieve...

I produce graphs with GPLOT at the end of the code from Labdata and in total there are over 25 variations of combinations of 2 variables which are needed as input: VISNUM and USUBJID. In order to avoid hardcoding and rerunning the program 25 times I want to use macros, also to prevent errors with hardcoding and a very long code. What I posted is one loop with one macro but in fact I need 2 loops ; a big one with a loop for VISNUM (within this loop 3 macros instead of 1) AND within that loop a smaller one with USUBJID. At the end I want to get 25 graphs.. 

I have written code which works with hardcoding, just simple code. Now I would like to convert that code to code inclusive macros. Sorry if I cannot share more info since this is sensitive and confidential 

[Tom · Accepted solution]

So you appear to have two values you want to pass. 

Do you want all values of VISIT combined with all values of SUBJECT?  Or do you just want the combinations that exist in your dataset?

So assuming you just want the combinations that appear in your dataset then create a macro that takes two parameters and then call it once for each combination in your dataset.

So let's assume the macro is named GPLOT and the parameters are named VISIT and SUBJECT.  So it might look like this:

%macro gplot(visit,subject);
proc gplot data=xxx;
  where visnum = "&visit" and usubjid = "&subject";
....
%mend;

Now if you have a dataset named HAVE which as all of the combinations of VISNUM and USUBJECT you want to use to call that macro then just use it to generate the macro calls.  One way is to use the CALL EXECUTE() function.

data _null_;
  set have;
  call execute(cats('%nrstr(%gplot)(',visnum,',',usubjid,')'));
run;

[JohanK]

Thanks so much Tom!!! It works fine... I am so happy to have learned more from your help.
Could I ask you a second small question? 

The next small 'issue' I am facing is that ODS Excel only stores the last graphs. So the last record from the macro. 

Here is the code for the macro , PROC GPLOT and ODS Excel. Probably I am not using some options correctly?  By the way, the dataset which I created is COH1 (all Subjects within Cohort 1)

What I would like is to have all graphs of all subjects on the same TAB. 

%macro gplot(subj);

goptions reset=all cback=white border htext=8pt htitle=12pt;

footnote;

axis1 order=(1 to 26 by 1)
value=(font='Arial/bold' height=8pt)
offset=(4,4)
width=2
label=none
major=none
minor=none;

axis2 order=(0 to 1 by .2, 5, 6 to &MAXUNL. by 2)
label=(angle=90 'x UNL')
value=(tick=5 ' ' tick=10 ' ')
offset=(2,2);
axis3 order=(0 to 1 by .2, 5, 6 to &MAXUNL. by 2)
label=(angle=90 'x UNL')
value=(tick=5 ' ' tick=10 ' ')
offset=(2,2);

symbol1 interpol=join width=1 color=vligb value=dot height=.8;
symbol2 interpol=join width=1 color=salmon value=dot height=.8;
symbol3 interpol=join width=1 color=vibg value=dot height=.8;
symbol4 interpol=join width=1 color=red value=dot height=.8;
symbol5 interpol=join width=1 color=purple value=dot height=.8;
symbol6 interpol=join width=1 color=blue value=dot height=.8;
symbol7 interpol=join width=1 color=yellow value=dot height=.8;
symbol8 interpol=join width=1 color=green value=dot height=.8;

symbol9 interpol=none value='' font='Arial/bold' color=black height=4pt;
symbol10 interpol=none value='' font='Arial/bold' color=black height=4pt;
symbol11 interpol=none value='' font='Arial/bold' color=black height=4pt;
symbol12 interpol=none value='' font='Arial/bold' color=black height=4pt;
symbol13 interpol=none value='' font='Arial/bold' color=black height=4pt;
symbol14 interpol=none value='' font='Arial/bold' color=black height=4pt;
symbol15 interpol=none value='' font='Arial/bold' color=black height=4pt;
symbol16 interpol=none value='' font='Arial/bold' color=black height=4pt;
title;

ODS EXCEL FILE = "C:\Users\jkwaaitaal\OneDrive - Molecular Partners AG\Documents\MP0317\MP0317_LAB LIVER_GRAPHICS_%sysfunc(date(),DATE9.).xlsx"
options(embedded_titles='on' sheet_interval='page' sheet_name="COHORT 1-LAB A");
title1 "MP0317_ASAT ALAT LDH ALP BILIRUBIN LIPASE_COHORT 1 SUBJECT MP0317-&SUBJ.";
proc gplot data=combi2; WHERE USUBJID="&SUBJ" ;
plot (ASAT ALAT LDH ALP BILIRUBIN BILIRUBIN_DIR BILIRUBIN_INDIR LIPASE)*visit / overlay haxis=axis1 vaxis=axis2
vref=3 13 cframe=grayee legend;
plot2 (ASAT ALAT LDH ALP BILIRUBIN BILIRUBIN_DIR BILIRUBIN_INDIR LIPASE)*visit / overlay vaxis=axis3;
format ASAT ALAT LDH ALP BILIRUBIN BILIRUBIN_DIR BILIRUBIN_INDIR LIPASE 4.1;
run;

axis1 order=(1 to 26 by 1)
value=(font='Arial/bold' height=8pt)
offset=(4,4)
width=2
label=none
major=none
minor=none;

axis2 order=(0 to 1 by .2, 5, 6 to &MAXUNL2. by 5)
label=(angle=90 'x UNL')
value=(tick=5 ' ' tick=10 ' ')
offset=(2,2);
axis3 order=(0 to 1 by .2, 5, 6 to &MAXUNL2. by 5)
label=(angle=90 'x UNL')
value=(tick=5 ' ' tick=10 ' ')
offset=(2,2);

symbol4 interpol=join width=1 color=vligb value=dot height=.8;
symbol5 interpol=join width=1 color=salmon value=dot height=.8;
symbol6 interpol=join width=1 color=vibg value=dot height=.8;

symbol9 interpol=none value='' font='Arial/bold' color=black height=4pt;
symbol10 interpol=none value='' font='Arial/bold' color=black height=4pt;
symbol11 interpol=none value='' font='Arial/bold' color=black height=4pt;

ods excel options(sheet_name="COHORT 1-LAB B");

title1 "MP0317_CRP CK AMYLASE_COHORT 1 SUBJECT MP0317-&SUBJ.";
proc gplot data=combi2; WHERE USUBJID="&SUBJ" ;
plot (CRP CK AMYLASE)*visit / overlay haxis=axis1 vaxis=axis2
vref=3 13 cframe=grayee legend;
plot2 (CRP CK AMYLASE)*visit / overlay vaxis=axis3 ;
format CRP CK AMYLASE 4.1;

run;
ODS EXCEL CLOSE;
%mend;

DATA _null_;
SET COH1;
CALL EXECUTE (CATS('%nrstr(%gplot)(',USUBJID,')'));
run;

[Tom]

Hard to read code pasted into the main body of the message instead of using the Insert SAS Code button.

But it looks like your logic issue is that you are overwriting the XLSX file by calling the ODS EXCEL statement inside the loop.  Instead just open the destination before making the reports and then close when you have finished.

For normal reports where you are writing tabular data to the Excel file you would open the XLSX file once and then perhaps issue separate ODS EXCEL OPTIONS(); statements to change the SHEET name that the next procedure would write to.  But I am not sure if that would work with PROC GPLOT.

[JohanK]

Thank you very much Tom! 

I changed from ODS Excel to ODS PDF , and also moved ODS close to the end of the code. It works fantastic! 

[Reeza]

%macro gplot(subj);

goptions reset=all cback=white border htext=8pt htitle=12pt;

footnote;

axis1 order=(1 to 26 by 1)
value=(font='Arial/bold' height=8pt)
offset=(4,4)
width=2
label=none
major=none
minor=none;

axis2 order=(0 to 1 by .2, 5, 6 to &MAXUNL. by 2)
label=(angle=90 'x UNL')
value=(tick=5 ' ' tick=10 ' ')
offset=(2,2);
axis3 order=(0 to 1 by .2, 5, 6 to &MAXUNL. by 2)
label=(angle=90 'x UNL')
value=(tick=5 ' ' tick=10 ' ')
offset=(2,2);

symbol1 interpol=join width=1 color=vligb value=dot height=.8;
symbol2 interpol=join width=1 color=salmon value=dot height=.8;
symbol3 interpol=join width=1 color=vibg value=dot height=.8;
symbol4 interpol=join width=1 color=red value=dot height=.8;
symbol5 interpol=join width=1 color=purple value=dot height=.8;
symbol6 interpol=join width=1 color=blue value=dot height=.8;
symbol7 interpol=join width=1 color=yellow value=dot height=.8;
symbol8 interpol=join width=1 color=green value=dot height=.8;

symbol9 interpol=none value='' font='Arial/bold' color=black height=4pt;
symbol10 interpol=none value='' font='Arial/bold' color=black height=4pt;
symbol11 interpol=none value='' font='Arial/bold' color=black height=4pt;
symbol12 interpol=none value='' font='Arial/bold' color=black height=4pt;
symbol13 interpol=none value='' font='Arial/bold' color=black height=4pt;
symbol14 interpol=none value='' font='Arial/bold' color=black height=4pt;
symbol15 interpol=none value='' font='Arial/bold' color=black height=4pt;
symbol16 interpol=none value='' font='Arial/bold' color=black height=4pt;
title;


title1 "MP0317_ASAT ALAT LDH ALP BILIRUBIN LIPASE_COHORT 1 SUBJECT MP0317-&SUBJ.";
proc gplot data=combi2; WHERE USUBJID="&SUBJ" ;
plot (ASAT ALAT LDH ALP BILIRUBIN BILIRUBIN_DIR BILIRUBIN_INDIR LIPASE)*visit / overlay haxis=axis1 vaxis=axis2
vref=3 13 cframe=grayee legend;
plot2 (ASAT ALAT LDH ALP BILIRUBIN BILIRUBIN_DIR BILIRUBIN_INDIR LIPASE)*visit / overlay vaxis=axis3;
format ASAT ALAT LDH ALP BILIRUBIN BILIRUBIN_DIR BILIRUBIN_INDIR LIPASE 4.1;
run;

axis1 order=(1 to 26 by 1)
value=(font='Arial/bold' height=8pt)
offset=(4,4)
width=2
label=none
major=none
minor=none;

axis2 order=(0 to 1 by .2, 5, 6 to &MAXUNL2. by 5)
label=(angle=90 'x UNL')
value=(tick=5 ' ' tick=10 ' ')
offset=(2,2);
axis3 order=(0 to 1 by .2, 5, 6 to &MAXUNL2. by 5)
label=(angle=90 'x UNL')
value=(tick=5 ' ' tick=10 ' ')
offset=(2,2);

symbol4 interpol=join width=1 color=vligb value=dot height=.8;
symbol5 interpol=join width=1 color=salmon value=dot height=.8;
symbol6 interpol=join width=1 color=vibg value=dot height=.8;

symbol9 interpol=none value='' font='Arial/bold' color=black height=4pt;
symbol10 interpol=none value='' font='Arial/bold' color=black height=4pt;
symbol11 interpol=none value='' font='Arial/bold' color=black height=4pt;

ods excel options(sheet_name="COHORT 1-LAB B");

title1 "MP0317_CRP CK AMYLASE_COHORT 1 SUBJECT MP0317-&SUBJ.";
proc gplot data=combi2; WHERE USUBJID="&SUBJ" ;
plot (CRP CK AMYLASE)*visit / overlay haxis=axis1 vaxis=axis2
vref=3 13 cframe=grayee legend;
plot2 (CRP CK AMYLASE)*visit / overlay vaxis=axis3 ;
format CRP CK AMYLASE 4.1;

run;

%mend;

ODS EXCEL FILE = "C:\Users\jkwaaitaal\OneDrive - Molecular Partners AG\Documents\MP0317\MP0317_LAB LIVER_GRAPHICS_%sysfunc(date(),DATE9.).xlsx"
options(embedded_titles='on' sheet_interval='page' sheet_name="COHORT 1-LAB A");
DATA _null_;
SET COH1;
CALL EXECUTE (CATS('%nrstr(%gplot)(',USUBJID,')'));
run;
ODS EXCEL CLOSE;

ODS statements are in the wrong location, you're constantly overwriting your file as ODS EXCEL Does not support appending to files.

[Kurt_Bremser]

Please post usable example data for dataset COHORT1 (because that is what you start with) in a DATA STEP WITH DATALINES (nothing else!!!) and show what you want to get out of it.

[donricardo]

Hi - there's a few things going on here that I'll mention:

1) you can't just place a macro call in a data step as you did because as SAS is scanning the data step, it's sending each line to the compiler - except for the macro trigger (%lab) in your program, which will be sent to the macro processor immediately and executed there.    

2) Never place a semi-colon on a macro call - we only use semi-colons on SAS statements and a macro call is technically NOT a SAS statement.

So I've tried to build a simple replica of your code as follows, which will work for you.   While you can place the whole thing into a macro definition and a %do statement, this will work here:

%macro lab7;
%put hello lab7, nice to meet you!;
%mend;

%lab7
%let maxvisnum = 10;

data mydata;
do until (vinum=&maxvisnum);
vinum+1;
call execute(%nrstr("%lab7"));
end;
run;

Which yields a nice log:

ata mydata;
33 do until (vinum=&maxvisnum);
34 vinum+1;
35 call execute(%nrstr("%lab7"));
36 end;
37 run;

hello lab7, nice to meet you!
hello lab7, nice to meet you!
hello lab7, nice to meet you!
hello lab7, nice to meet you!
hello lab7, nice to meet you!
hello lab7, nice to meet you!
hello lab7, nice to meet you!
hello lab7, nice to meet you!
hello lab7, nice to meet you!
hello lab7, nice to meet you!
NOTE: The data set WORK.MYDATA has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

A bit of explanation as well:   CALL EXECUTE permits you to send any code to the Input Stack when as needed.  The %nrstr function delays the execution of the %lab7 macro call, instead just stacking up the call 10 times in the Input Stack.   It's a bit advanced, but this gets the job done for you.