Let Statement Inside Macro Not Assigning Value

mhoward2 — Fri, 27 Jan 2023 17:45:05 GMT

Hi all, I am attempting to assign a macro variable that is essentially a string of SQL code, but SAS is not assigning it for some reason. Here is my code:

%MACRO DETERMINE_SOURCE_TABLE();
%IF &Inc_End. = &End_YYYY.0331 %THEN %DO;
	PROC SQL;
		CREATE TABLE WORK.Table2 AS	
			SELECT DISTINCT
				t1.CATEGORY,
				t1.PREFERENCE
				MIN(t1.EFF_DATE) AS MIN_OF_DATE
		FROM WORK.Table1 T1
			WHERE t1.DRUG_CATEGORY ~= '<NA>'
			GROUP BY 1,2,3,4,5,6,7,8,9,10,11;
	QUIT;


	/*GET RID OF LAST 3 MONTHS OF CLAIMS FROM PREVIOUS YEAR*/
	PROC SQL;
		CREATE TABLE WORK.Table3 AS	
		SELECT DISTINCT t1.*
		FROM  WORK.Table1 T1
		WHERE t1.EFF_DATE_SK GE &Inc_End_YYYY.0101;
	QUIT;


%LET Source = t2.MIN_OF_DATE AS MIN_OF_DATE
			  FROM WORK.Table3 t1 LEFT JOIN
				   WORK.Table2 t2 ON t1.CATEGORY = t2.CATEGORY AND
									t1.PREFERENCE = t2.PREFERENCE
			  WHERE t1.CATEGORY NE '<NA>'
			  GROUP BY 1,2;
%END;  	


%IF &Inc_End. > &End_YYYY.0331 %THEN %DO;
	%LET Source = MIN(t1.EFF_DATE_SK) AS MIN_OF_DATE
				  FROM WORK.Table1 t1
				  WHERE t1.CATEGORY NE '<NA>'
				  GROUP BY 1,2;
%END;

%MEND DETERMINE_SOURCE_TABLE;
%DETERMINE_SOURCE_TABLE
%PUT &Source;

As you can see, when my first if Statement is true, I do some code, but when my second if Statement is true, I just set the variable. For the code im running the first if statement is true but the variable is not setting correctly. Here is from the log

MLOGIC(DETERMINE_SOURCE_TABLE):  Beginning execution.
88         %PUT &Source;
SYMBOLGEN:  Macro variable INC_END resolves to 20221231
SYMBOLGEN:  Macro variable END_YYYY resolves to 2022
MLOGIC(DETERMINE_SOURCE_TABLE):  %IF condition &Inc_End. = &End_YYYY.0331 is FALSE
SYMBOLGEN:  Macro variable INC_END resolves to 20221231
SYMBOLGEN:  Macro variable END_YYYY resolves to 2022
MLOGIC(DETERMINE_SOURCE_TABLE):  %IF condition &Inc_End. > &End_YYYY.0331 is TRUE
MLOGIC(DETERMINE_SOURCE_TABLE):  %LET (variable name is SOURCE)
MLOGIC(DETERMINE_SOURCE_TABLE):  Ending execution.
WARNING: Apparent symbolic reference SOURCE not resolved.
&Source

Any thoughts? Thanks!

Re: Let Statement Inside Macro Not Assigning Value

PaigeMiller — Fri, 27 Jan 2023 17:49:04 GMT

&source is defined and given a value in the macro, and it is not available outside the macro.

To change this, you could insert this line into the macro

%global source;

Re: Let Statement Inside Macro Not Assigning Value

mhoward2 — Fri, 27 Jan 2023 17:50:53 GMT

Just fade me fam.

Thank you so much!! I was fully aboard the struggle bus.

topic Let Statement Inside Macro Not Assigning Value in SAS Programming

Let Statement Inside Macro Not Assigning Value

Re: Let Statement Inside Macro Not Assigning Value

Re: Let Statement Inside Macro Not Assigning Value