Using Macro Variables in Proc Sql

[bignate1030]

Currently I have this code: 

PROC SQL;
CREATE TABLE LYMPOST_A AS 
	SELECT * FROM WORK.KVS_EVENT_DATA t1
		WHERE t1.KEY LIKE 'LYMPOST_DS_LYM_DIS_STAT_BY_CT%'
		ORDER BY PERSON_ID, MAIN_EVENT_ID, EVENT_ID;
QUIT;

I would like to replace the variable name in the LIKE statement with a macro variable, something like:

%LET VARNAME1 = LYMPOST_DS_LYM_DIS_STAT_BY_CT;

PROC SQL;
CREATE TABLE LYMPOST_A AS 
	SELECT * FROM WORK.KVS_EVENT_DATA t1
		WHERE t1.KEY LIKE 'VARNAME1%'
		ORDER BY PERSON_ID, MAIN_EVENT_ID, EVENT_ID;
QUIT;

This code creates a table but does not populate it. Any help is appreciated

[PeterClemmensen · Accepted solution]

Use an & to refer to the macro variable and put it in double quotation marks to resolve the macro variable

%LET VARNAME1 = LYMPOST_DS_LYM_DIS_STAT_BY_CT;

PROC SQL;
CREATE TABLE LYMPOST_A AS 
	SELECT * FROM WORK.KVS_EVENT_DATA t1
		WHERE t1.KEY LIKE "&VARNAME1.%"
		ORDER BY PERSON_ID, MAIN_EVENT_ID, EVENT_ID;
QUIT;

[bignate1030]

Now I have a data set where the variable I'm interested in is populated with values that are variations on the 'LYMPOST_DS_LYM_DIS_STAT_BY_CT' variable such as: 

'LYMPOST_DS_LYM_DIS_STAT_BY_CT' 

'LYMPOST_DS_LYM_DIS_STAT_BY_CT_6M'

'LYMPOST_DS_LYM_DIS_STAT_BY_CT_ALL_HIGH'

'LYMPOST_DS_LYM_DIS_STAT_BY_CT_ONE_LOW'

I would like to delete some rows specific to the variation on the name using code like:

PROC SQL;
DELETE FROM SINGLE_VAR t1
 	WHERE t1.KEY = 'LYMPOST_DS_LYM_DIS_STAT_BY_CT';

DELETE FROM SINGLE_VAR t1
 	WHERE t1.KEY LIKE 'LYMPOST_DS_LYM_DIS_STAT_BY_CT_ONE%';

DELETE FROM SINGLE_VAR t1
 	WHERE t1.KEY LIKE 'LYMPOST_DS_LYM_DIS_STAT_BY_CT_ALL%';

QUIT;

However now I would like to use macros. I cannot simply use "&varname1" because I would like to keep the 'LYMPOST_DS_LYM_DIS_STAT_BY_CT_6M' value and others like it. 

[Tom]

Use the same method.

Please show what you tried and explain how it didn't work.

[bignate1030]

PROC SQL;
DELETE FROM SINGLE_VAR t1
 	WHERE t1.KEY = "&VARNAME1";
DELETE FROM SINGLE_VAR t1
 	WHERE t1.KEY LIKE "&VARNAME1_ONE%";
DELETE FROM SINGLE_VAR t1
 	WHERE t1.KEY LIKE "&VARNAME1_ALL%";
QUIT;

This is what I tried and it throws the warning "Apparent symbolic reference VARNAME1_ONE not resolved" (Same for VARNAME1_ALL). 

[Tom]

To prevent that you need to add a period after the end of the macro variable name.  Otherwise the macro processor will keep using any character that can validly be used in a name as part of the variable name.

WHERE t1.KEY LIKE "&VARNAME1._ONE%";

Note this means that if you want to insert an actual period after the value of the macro variable then you need to type two of them since the first one will be used by the macro processor as part of the macro variable reference.