BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
xliu1
Quartz | Level 8

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;
1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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

 

 

 

 

 

 

--
Paige Miller

View solution in original post

12 REPLIES 12
Patrick
Opal | Level 21

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

xliu1
Quartz | Level 8

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

Kurt_Bremser
Super User

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
Super User Tom
Super User

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
Quartz | Level 8

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.

xliu1_0-1595087086306.png

 

Tom
Super User Tom
Super User

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

xliu1
Quartz | Level 8

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
Super User Tom
Super User
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
Quartz | Level 8

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
Diamond | Level 26

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

 

 

 

 

 

 

--
Paige Miller
xliu1
Quartz | Level 8

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 10087 views
  • 3 likes
  • 5 in conversation