Assign a list values to a macro variable in sas

[xliu1]

I try to assign a list values to a macro variable, but somehow sas does not pick up all the values in that variable. How should I modify the code for %let statement? Thanks.

%let admdate (LIST= 201505 201508 201601)

proc sql;
create table T1 as
select id,
major,
college

from Enrollment
where term ='&admdate';
quit;

[Patrick]

By making quite a few assumptions like that your variable term contains a numerical value as you show us and is not a SAS Date value below could work.

SAS macro variables must be in double quotes to resolve.

%let admdate=201505, 201508, 201601;

proc sql;
  create table T1 as
    select 
      id,
      major,
      college
    from Enrollment
    where term in (&admdate)
  ;
quit;

Fixed as per @Kurt_Bremser's feedback

[Kurt_Bremser]

If term is actually a non-formatted numeric variable, there must not be quotes around the macro variable call.

[xliu1]

Thank you for your response. The term is a character variable. Putting comma in between does not work. 

[Kurt_Bremser]

Rule #1 for using macro language: start with working code without any macro use.

So you should first create the proper SQL code for the IN operator.

Please run a PROC CONTENTS on your dataset and show us the line that describes the variable term.

Big hint: macro variables are not resolved when enclosed in single quotes, but that alone won't fix your code.

[Tom]

The = operator is for comparing to a single value. To test if a value is in a list use, wait for it, the IN operator.

If your variable is a character string then use quoted values in your macro variable.

%let admdate = "201505"  "201508"  "201601" ;
...
where term in (&admdate)

Note that the IN operator in SAS does NOT require commas. You can use either comma or space or mix them.

[xliu1]

Thank you for your response. I tried your syntax but I get this error message in sas log. Any idea on what is wrong? Thanks.

[Tom]

Why did you add the extra quotes at the beginning and end?

[xliu1]

I did not add extra quotes. here is my full sas syntax. I got that error message when I run the codes.

%LET ERTERM = 201808;
%LET ADMDATE = "201805" "201808";
%LET RETNTERM = 201908;
PROC SQL;

Create Table Journalism_Enrollment As
SELECT  DISTINCT TERN.UID
, TERN.TERMID AS TERM
, TERN.STU_RECENT_ADM_DT AS COHORT
, TERN.STU_RECENT_ADM_TYP
, TERN.STU_CLASS_LEVEL
, TERN.MAJOR
, TERN.MajorConcentration
, TERN.COLLEGE
, (SELECT RETN.TERMID
	FROM SID.SID_ENROLLMENTS RETN
	WHERE RETN.UID = TERN.UID
	AND RETN.BENCH = 'E'
	AND RETN.FEE_CLASS_KIND NOT LIKE 'P'
	AND RETN.TERMINATION_CD NOT LIKE 'D'
	AND RETN.HOMECAMPUS = '0004'
	AND RETN.TERMID = "&RETNTERM") AS RETAINED_TO_FOLLOWING_FALL

FROM SID.SID_ENROLLMENTS_All_Vw TERN
WHERE TERN.BENCH = 'E'
AND TERN.FEE_CLASS_KIND NOT LIKE 'P'
AND TERN.TERMINATION_CD NOT LIKE 'D'
AND TERN.HOMECAMPUS = '0004'
AND TERN.STU_RECENT_ADM_DT in ("&ADMDATE")
AND TERN.TERMID = "&ERTERM"
ORDER BY TERN.UID;

Quit;

[Tom]

AND TERN.STU_RECENT_ADM_DT in ("&ADMDATE")
                               ^        ^

I put the ^ under the places where you have added extra quote characters.

PS If you want a tool that will make it easier to go from a space delimited list of unquoted values to quoted list of values try this macro.  https://github.com/sasutils/macros/blob/master/qlist.sas

[xliu1]

Thank you. I see your points. I removed those quotes and syntax work. It picked all the observations as it is supposed to pick. I am very new to sas macro. Shouldn't the macro variable be executed in double quotes?

[PaigeMiller · Accepted solution]

---

@xliu1 wrote:

Shouldn't the macro variable be executed in double quotes?

---

The macro processor has to produce valid correct working SAS code. When it does not produce valid working SAS code you get errors. So let's go through this in detail.

First, you set the value of the macro variable

%LET ADMDATE = "201805" "201808";

Then you go to use it with the code

AND TERN.STU_RECENT_ADM_DT in ("&ADMDATE")

so &ADMDATE is replaced by its value "201805" "201808" which yields

AND TERN.STU_RECENT_ADM_DT in (""201805" "201808"")

and this is not legal valid working SAS code ... can you see why?

If instead of

in ("&admdate")

you remove the quotes, then without the quotes you get

AND TERN.STU_RECENT_ADM_DT in ("201805" "201808")

and this IS working valid SAS code.

If you're not sure why one is valid SAS code and one is not, then you need to stop working with macros, and learn more about PROC SQL and related base SAS code. If you don't know what is and is not valid SAS code, then you will always get stuck writing macros.

[xliu1]

Thank you very much for the explanations. It does help me understand how macro works!