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..
_
22
76
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..
_
22
200
NOTE: Line generated by the macro variable "Q17".
75 F_.
___
22
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.
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.
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.
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(¶m)=,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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.