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.
... View more