BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
mhoward2
Obsidian | Level 7

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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

  

--
Paige Miller

View solution in original post

2 REPLIES 2
PaigeMiller
Diamond | Level 26

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

  

--
Paige Miller
mhoward2
Obsidian | Level 7
Just fade me fam.

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

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1570 views
  • 0 likes
  • 2 in conversation