topic Re: DATA Null not resolving within Macro in SAS Programming

DATA Null not resolving within Macro

smilbuta — Tue, 12 Jun 2018 16:54:22 GMT

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.

Re: DATA Null not resolving within Macro

PaigeMiller — Tue, 12 Jun 2018 16:58:57 GMT

Perhaps this step is never true:

if "&TBL_BUILD" = "Summer_Table"

Re: DATA Null not resolving within Macro

Reeza — Tue, 12 Jun 2018 16:59:20 GMT

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.

Re: DATA Null not resolving within Macro

Kurt_Bremser — Tue, 12 Jun 2018 17:03:51 GMT

Look at the log. You are comparing an integer 1 (resolved from &termv) with the string '1'.

Re: DATA Null not resolving within Macro (RESOLVED)

smilbuta — Tue, 12 Jun 2018 17:32:51 GMT

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");

Re: DATA Null not resolving within Macro (RESOLVED)

Tom — Tue, 12 Jun 2018 17:42:20 GMT

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"

Re: DATA Null not resolving within Macro (RESOLVED)

smilbuta — Tue, 12 Jun 2018 17:51:24 GMT

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.

Re: DATA Null not resolving within Macro (RESOLVED)

Kurt_Bremser — Tue, 12 Jun 2018 17:58:48 GMT

@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.

Re: DATA Null not resolving within Macro (RESOLVED)

PaigeMiller — Tue, 12 Jun 2018 18:00:12 GMT

@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;

Re: DATA Null not resolving within Macro (RESOLVED)

smilbuta — Tue, 12 Jun 2018 18:23:11 GMT

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.

Re: DATA Null not resolving within Macro (RESOLVED)

PaigeMiller — Tue, 12 Jun 2018 20:28:18 GMT

@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.