topic How can I conditionally run either MacroA or MacroB based on a do loop in SAS Programming

How can I conditionally run either MacroA or MacroB based on a do loop

KMU — Mon, 11 Feb 2019 16:19:48 GMT

Hi All,

I've been writing SAS code for about a month now so obviously don't know everything and I was wondering if anyone had any thoughts about why my code won't execute correctly.

I am essentially trying to make it so that if a specific variable is missing ('.') then one macro runs and if it is not missing then another macro runs. I have tried various iterations of the if statement and it still won't run the first macro when a value is missing. In the below example the variable is only missing when i=2. I have only included the first 3 Questions but there are a total of 116 just for context.

There are 3 data sets being used in this code QCROSSREF1718 is just a list of the question numbers for 2017 and 2018, Survey1 is the responses to a survey for 2018 and Survey2 is the responses to the survey from 2017.

Please see code, log and example data sets below. Apologies for the long code. Thanks in advance.

options spool;

proc sql noprint;
		select count(*)
 			into :NObs
		from work.QCROSSREF1718;

		select Q18
			into :Q18Y1-:Q18Y%left(&NObs)
		from work.QCROSSREF1718;

		select Q17
 			into :Q17Y1-:Q17Y%left(&NObs)
		from work.QCROSSREF1718;

		select DESC18
 			into :QDesc1-:QDesc%left(&NObs)
		from work.QCROSSREF1718;

		select Weight18
			into :QYWeight1-:QYWeight%left(&NObs)
		from work.QCROSSREF1718;

		select Weight17
			into :Q17YWeight1-:Q17YWeight%left(&NObs)
		from work.QCROSSREF1718;
quit;

/*Problem code*/

%macro Umbrella;
	%do i=1 %to 3;	
	%let Q17 = &&Q17Y&i;
	
		%if &Q17='.' %then %do;
			%only18;
		%end;
		%if &Q17~='.' %then %do;
			%comp1718;
		%end;

	%end;
%mend Umbrella;

%Umbrella;

/*macro to run if value is not missing*/

%macro Comp1718;
	%do i=1 %to 3;

		%let Q18 = &&Q18Y&i;
		%let Q17 = &&Q17Y&i;
		%let QD = &&QDesc&i;
		%let Q18Weight=&&QYWeight&i;
		%let Q17Weight=&&Q17YWeight&i;
		
		        /*2018*/
			PROC SURVEYFREQ data=work.Survey1 nosummary;
				tables &QD
				/ row nofreq nostd nowt nototal;
				weight &Q18Weight;
				ods output OneWay=CrossTab18;
			run;
			
								
			/*2017*/
			PROC SURVEYFREQ data=Work.Survey2 nosummary;
				tables &Q17
				/ row nofreq nostd nowt nototal;
				weight &Q17Weight;
				ods output ONEWAY=CrossTab17;
			run;
									
			/*Join tables*/
			PROC SQL;
			   CREATE TABLE WORK.Comparison&Q18 AS 
			   SELECT t1.&QD, 
		          t2.PERCENT17, 
		          t1.PERCENT18,
			  /*Calculate percent difference*/
		          (t1.Percent-t2.Percent) AS Percent_Difference
      			FROM WORK.CrossTab18 t1
           			Full JOIN WORK.CROSSTAB17 t2 ON (t1.&QD = t2.&Q17);
			QUIT;
	%end;
%mend Comp1718;

/*macro to run if value is missing*/

%macro only18;
	%do i=1 %to 3;

		%let Q18 = &&Q18Y&i;
		%let QD = &&QDesc&i;
		%let Q18Weight=&&QYWeight&i;
				
		        PROC SURVEYFREQ data=work.Survey1 nosummary;
				tables &QD
				/ row nofreq nostd nowt nototal;
				weight &Q18Weight;
				ods output OneWay=CrossTab18;
			run;
		
			PROC SQL;
				CREATE TABLE WORK.Comparison&Q18 AS
				SELECT t.&QD,
				t.PERCENT
      			FROM WORK.CrossTab18;
			QUIT;			
	%end;
%mend only18;

LOG Errors below (showing that it is still just running %Comp1718 in spite of %umbrella if statements

#### Q18=F12 ####

#### Q17=. ####

#### Q18Weight = F12Weight ####

#### Q17Weight = . ####

NOTE: Line generated by the macro variable "Q17".

75 t1..

ERROR 22-322: Syntax error, expecting one of the following: a name, *.

ERROR 76-322: Syntax error, statement will be ignored.

ERROR: WHERE clause operator requires compatible variables.

NOTE: PROCEDURE SURVEYFREQ

NOTE: The SAS System stopped processing this step because of errors.

NOTE: Line generated by the macro variable "Q17".

75 t2..

200

NOTE: Line generated by the macro variable "Q17".

75 F_.

___

201

ERROR 22-322: Syntax error, expecting one of the following: a name, ;, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_.

ERROR 201-322: The option is not recognized and will be ignored.

Data Examples below:

QCROSSREF1718 (this essentially just gives the columns titles needed from the two main data sets. The reason for all the different columns is that the naming conventions between years are not consistent)

Q18 Q17 DESC18 WEIGHT18 WEIGHT17

F11 F11 F11_Desc F11Weight F11_Weight

F12 . F12_Desc F12Weight .

F13 F12 F13_Desc F13Weight F12_Weight

Survey1 (this is fictitious data for example, the real data set has 18000 records)

F11 F11_Desc F11Weight F12 F12_Desc F12Weight F13 F13_Desc F13Weight

1 Yes 0.5689 0 No 0.2525 2 Sometimes 0.7895

0 No 0.5689 0 No 0.5698 1 Yes 0.2689

1 Yes 0.4475 2 Sometimes 0.6896 0 No 0.7895

2 Sometimes 0.6989 1 Yes 0.5698 1 Yes 0.5887

0 No 0.4475 1 Yes 0.2525 2 Sometimes 0.3698

Survey2 (this is fictitious data for example, the real data set has 17000 records)

F11 F11_Weight F12 F12_Weight

Yes 0.3658 Yes 0.6989

No 1.0001 Yes 0.9875

Sometimes 0.3587 No 0.4578

Yes 0.2151 Sometimes 0.3457

No 0.5877 Yes 0.4578

Please feel free to ask for any clarifications etc, I tried to make this as clear as possible but this is my first time posting so might not be.

Re: How can I conditionally run either MacroA or MacroB based on a do loop

ChrisNZ — Tue, 12 Feb 2019 20:45:18 GMT

One thing that sticks out is that this should probably be

%if &Q17=. %then %do;

instead of

%if &Q17='.' %then %do;

Macro variables are always strings and needn't (shouldn't really) be quoted.

Re: How can I conditionally run either MacroA or MacroB based on a do loop

ballardw — Tue, 12 Feb 2019 20:52:08 GMT

One issue you have is that you use the same macro variable name to control loops inside macros called by another macro. If you are going to do so you need to define the loop variable as %local before using it. Other wise the loop counter also gets set and incremented by the macros called in your outer macro %umbrella.

Then

%if &Q17='.' %then %do;

does not test if the macro variable Q17's value is missing, it tests to see if the value is actually '.'.

A macro I borrowed a long time ago:

/* macro to test macro parameters             */
/* returns 1 if the tested parameter is blank */
/* 0 otherwise, blank means all charaters are,*/
/* or are macro variables that resolve to a,  */
/* blank                                      */
/* param can be upto 65,531 characters long   */
/* if numeric and several 1000 digits long may*/
/* hang the session. (Windows 32 bit OS)      */
/* NOT a test for a NULL (zero length string) */
/* though may work for some of those as well  */

%macro isBlank(param);
%sysevalf(%superq(&param)=,boolean)
%mend isBlank;

Using that macro you would use

%if %isblank(Q17) %then %do <whatever when the macro variable is blank>. Or you can use explicit comparison with 1 (blank) or 0 (not blank)

Note that there is NO & for the macro variable. If you pass something that is not the name of a macro variable there will be a Warning about unresolved symbolic reference.

You will want to learn to set the system options Mprint and/or symbolgen and/or mlogic before executing macros while debugging them. Mprint will show the generated program text. The error messages generally make more sense in that context. Symbolgen will go through the resolution steps of creating values from macro variables. Mlogic shows results of logic constructs such as %if %then.

Re: How can I conditionally run either MacroA or MacroB based on a do loop

mkeintz — Tue, 12 Feb 2019 21:42:34 GMT

Because macro language (unlike SAS language) is a text-based language, you don't need quotes to specify a character literal value. So macro doesn't use a pair of ' characters to designate a character literal. Just use the character literal unquoted.

I.e. this statement

%if &Q17='.' %then %do;

will fail, because the macrovar Q17 does NOT have quotes - and therefore doesn't match '.'. Instead I suggest

%if &Q17=. %then %do;
	%only18;
%end;
%else  %do;
	%comp1718;
%end;

Also you can make your initial proc sql much more efficient, by simulataneously does all the select INTO expressions, as in:

proc sql noprint;
		select Q18,
		       Q17,
		       Q17,
		       Weight18,
		       Weight17
        into 
			:Q18Y1-:Q18Y999,
 			:Q17Y1-:Q17Y999,
 			:QDesc1-:QDesc999,
			:QYWeight1-:QYWeight999,
			:Q17YWeight1-:Q17YWeight999

		from work.QCROSSREF1718;
quit;

Note that I use 999 as the upper limit instead of NOBS. Don't worry. If there are fewer than 999 observations in QCROSSREF1718 then the "extra" macrovars will not be generated. And, as a bonus, there will also be produced a new macrovar: SQLOBS, which is the number of observations produced in the select statement. So you can use &SQLOBS as an upper limit to any subsequent do loop you need to implement.

Just be sure to use a number (I used 999) that is not less than the maximum expected number of observations.

Re: How can I conditionally run either MacroA or MacroB based on a do loop

KMU — Wed, 13 Feb 2019 10:59:05 GMT

Thanks everyone for your help I had to modify the code a little on top of your suggestions but it is now working as expected.