Hello Community,
I am having an issue where a Data null step witin a macro is not resolving...
Here is the abridged code i am building..
%MACRO BLDTERM(TBL_BUILD,COHORT_YEAR,PRELIM,TERM_ID); data _null_; if "&TBL_BUILD" = "Summer_Table" then do; CALL SYMPUT('TERMV','1'); end; run; Proc SQl; Creat table TEST as SELECT ..... [extra columns removed for clarity] /*Summer*Flags*/ CASE WHEN &TERMV = '1' THEN 1 ELSE 0 END format=8. AS SUMMER_ALL, CASE WHEN &TERMV = '1' AND &DSNAME..CF_FPTIME_IND = 'F' THEN 1 ELSE 0 END format=8. AS SUMMER_FULL_TIME, CASE WHEN &TERMV = '1' AND &DSNAME..CF_FPTIME_IND = 'P' THEN 1 ELSE 0 END format=8. AS SUMMER_PART_TIME, ..... [extra columns removed for clarity] QUIT;
%MEND BLDTERM;
The Log Says my submitted code looks as follows...
CASE WHEN &TERMV = '1' THEN 1 ELSE 0 END format=8. AS SUMMER_ALL,
CASE WHEN &TERMV = '1' AND PRELIM_DATA_DIM.CF_FPTIME_IND = 'F' THEN 1 ELSE 0 END format=8. AS UMMER_FULL_TIME,
CASE WHEN &TERMV = '1' AND PRELIM_DATA_DIM.CF_FPTIME_IND = 'P' THEN 1 ELSE 0 END format=8. AS SUMMER_PART_TIME,
The &TERMV variable neverver resolves to the integer so all my Cases resolve to 0
Any assistance is greatly apreciated.
Perhaps this step is never true:
if "&TBL_BUILD" = "Summer_Table"
The comparison is case sensitive, so summer_build is not the same as Summer_Build. Could that be your issue?
Can you post your full log with the debugging options turned on:
options mprint symbolgen;
@smilbuta wrote:
Hello Community,
I am having an issue where a Data null step witin a macro is not resolving...
Here is the abridged code i am building..
%MACRO BLDTERM(TBL_BUILD,COHORT_YEAR,PRELIM,TERM_ID); data _null_; if "&TBL_BUILD" = "Summer_Table" then do; CALL SYMPUT('TERMV','1'); end; run; Proc SQl; Creat table TEST as SELECT ..... [extra columns removed for clarity] /*Summer*Flags*/ CASE WHEN &TERMV = '1' THEN 1 ELSE 0 END format=8. AS SUMMER_ALL, CASE WHEN &TERMV = '1' AND &DSNAME..CF_FPTIME_IND = 'F' THEN 1 ELSE 0 END format=8. AS SUMMER_FULL_TIME, CASE WHEN &TERMV = '1' AND &DSNAME..CF_FPTIME_IND = 'P' THEN 1 ELSE 0 END format=8. AS SUMMER_PART_TIME, ..... [extra columns removed for clarity] QUIT;
%MEND BLDTERM;The Log Says my submitted code looks as follows...
CASE WHEN &TERMV = '1' THEN 1 ELSE 0 END format=8. AS SUMMER_ALL,
CASE WHEN &TERMV = '1' AND PRELIM_DATA_DIM.CF_FPTIME_IND = 'F' THEN 1 ELSE 0 END format=8. AS UMMER_FULL_TIME,
CASE WHEN &TERMV = '1' AND PRELIM_DATA_DIM.CF_FPTIME_IND = 'P' THEN 1 ELSE 0 END format=8. AS SUMMER_PART_TIME,
The &TERMV variable neverver resolves to the integer so all my Cases resolve to 0
Any assistance is greatly apreciated.
Look at the log. You are comparing an integer 1 (resolved from &termv) with the string '1'.
Thanks for the guidance,
It was the '1' and "1" I swear, quotes are going to kill me one day....
The folloing code now works...
%MACRO BLDTERM(TBL_BUILD,COHORT_YEAR,PRELIM,TERM_ID); data _null_; if "&TBL_BUILD" = "SUMMER_TBL_2" then do; CALL SYMPUT('TERMV','1'); end; run; data _null_; if "&prelim" = "P" then do; CALL SYMPUT('DSNAME','PRELIM_SDCF_DATA_DIM'); end; else do; CALL SYMPUT('DSNAME','SDCF_DATA_DIM'); end; run; proc sql; create table work.&TBL_BUILD as select &COHORT_YEAR format=$9. AS COHORT_YEAR, CASE WHEN "&TERMV" = "1" THEN 1 ELSE 0 END format=8. AS SUMMER_ALL, CASE WHEN "&TERMV" = "1" AND &DSNAME..CF_FPTIME_IND = 'F' THEN 1 ELSE 0 END format=8. AS SUMMER_FULL_TIME, CASE WHEN "&TERMV" = "1" AND &DSNAME..CF_FPTIME_IND = 'P' THEN 1 ELSE 0 END format=8. AS SUMMER_PART_TIME from DIM.&DSNAME; quit; %MEND BLDTERM; %BLDTERM(SUMMER_TBL_2,"&COHORT_YEAR",F,"&SUMMER_TERM_ID");
In SAS code there is no difference between "1" and '1'. Both define the one character string literal.
There is a big difference between 1 and '1'. One is a number and the other is a string.
CASE WHEN "&TERMV" = '1' THEN
In macro code quotes are really just part of the string, not required syntax to let the parser know you meant a string literal. So this test will fail.
%if "1" = '1'
You could let the macro processor treat both as numbers.
%if &termv = 1
Or add the double quotes around both.
%if "&termv" = "1"
So it was my Variable in the Case that was resolving to the numeric 1? I thought by default callsymput resolves to a string?
When i double quoted all it worked.
@smilbuta wrote:
So it was my Variable in the Case that was resolving to the numeric 1? I thought by default callsymput resolves to a string?
When i double quoted all it worked.
Everything created from the macro preprocessor is text, but it is program text, not data text (aka strings). So if the macro variable contains a single character 1, that is placed in the code and interpreted by SQL as a number. Putting the macro variable in quotes lets the SQL see a string.
@smilbuta wrote:
Thanks for the guidance,
It was the '1' and "1" I swear, quotes are going to kill me one day....
Then why even use quotes in this case around these variables?
%MACRO BLDTERM(TBL_BUILD,COHORT_YEAR,PRELIM,TERM_ID);
%if &tbl_build=SUMMER_TBL_2 %then %do;
proc sql;
create table work.&tbl_build as
1 as summer_all,
case when &dsname..cf_fptime_ind = 'F' then 1 else 0 end as summer_full_time,
...
quit;
%end;
%mend;
I know its confusing but the code actualy parces all three terms Fall, Summer, Spring.
The columns in my example simply reflected the summer action, The data null step has all three terms being evaluated. so based on the term parced, it makes the term table accordingly and flags the respective summer , fall or spring coulmns accordingly.
My examples was trimmed down to 1 term as it was occuring across all three.
@smilbuta wrote:
I know its confusing but the code actualy parces all three terms Fall, Summer, Spring.
The columns in my example simply reflected the summer action, The data null step has all three terms being evaluated. so based on the term parced, it makes the term table accordingly and flags the respective summer , fall or spring coulmns accordingly.
My examples was trimmed down to 1 term as it was occuring across all three.
None of this addresses why quoted integers are needed, the entire code could be written without creating values of a macro variable that are '1' or "1", but with %if statements.
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.