topic Re: Using Macro Variables in Proc Sql in SAS Programming

Using Macro Variables in Proc Sql

bignate1030 — Thu, 27 Jun 2019 14:42:19 GMT

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

Re: Using Macro Variables in Proc Sql

PeterClemmensen — Thu, 27 Jun 2019 14:43:58 GMT

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;

Re: Using Macro Variables in Proc Sql

bignate1030 — Thu, 27 Jun 2019 15:04:53 GMT

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.

Re: Using Macro Variables in Proc Sql

Tom — Thu, 27 Jun 2019 15:08:43 GMT

Use the same method.

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

Re: Using Macro Variables in Proc Sql

bignate1030 — Thu, 27 Jun 2019 15:28:00 GMT

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

Re: Using Macro Variables in Proc Sql

Tom — Thu, 27 Jun 2019 15:36:23 GMT

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.