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