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

Quick Question:

 

Let's say we have a variable named ZIP and we want a macro that does offer an early filter on specific zip codes:

I am aware that %LET works with text...

 

How I can work it out with numbers having something like that:

 

%LET Zip = 28201, 28203, 28204, 28205;

 

and then how we call it...

 

...

AND X.ZIP IN (&Zip.)

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

There are only two data types in SAS:  character and numeric.  So that error messing is confirming that ZIP is a character variable.  If you want the items in your IN list to be character, you need to put them in quotes:

 


and x.zip in ("28201", "28203", "28204", "28205")

Commas are optional, and macro language has nothing to do with it.

View solution in original post

8 REPLIES 8
Tom
Super User Tom
Super User

Not sure what the question is.

Personally I wouldn't include the commas.  The IN operator in SAS doesn't need them.

 

Also ZIP should really be a CHAR variable and not a numeric variable.  Otherwise zip code in the NorthEast look funny without the leading zeros and there is no way to handle ZIP+4 codes.

PaigeMiller
Diamond | Level 26

Two key points here:

 

  1. numbers are text in the macro processor
  2. when you use the macro variable and the macro variable is resolved into text, the the SAS code must be valid legal SAS code

 

So, if X.ZIP is numeric, the valid SAS code is:

 

and x.zip in (28201, 28203, 28204, 28205)

Thus, if you use the %LET statement as you wrote it, I believe it should work.

 

... we want a macro that does ...

 

Also, for complete clarity, and to avoid miscommunications in the future, do not refer to this as a macro. The variable &ZIP is a macro variable, not a macro. The %LET command is a macro command, not a macro.

--
Paige Miller
DanielLangley
Quartz | Level 8

If you are using it with proc sql then yes, that is correct.

If you are calling it within anything else then you probably don't need the commas.

Tom
Super User Tom
Super User

PROC SQL doesn't need commas in IN operator lists either.  

1     proc sql ;
2     select count(*) from sashelp.class
3     where age in (12 13)
4     ;
5     quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.24 seconds
      cpu time            0.09 seconds

Unless you are explicitly writing SQL for a foreign database by using EXECUTE or CONNECTION TO.

triunk
Obsidian | Level 7

It still show an error saying in both cases (with comas and not comas and with let and/or hard code in proc sql):

 

"Expression using IN has components that are of different data types"

PaigeMiller
Diamond | Level 26

@triunk wrote:

It still says in both cases (with comas and not comas and with let and/or hard code in proc sql):

 

"Expression using IN has components that are of different data types"


It would say that if ZIP is actually a character variable? Is it?

--
Paige Miller
triunk
Obsidian | Level 7

It is a character... thank you and my apologies to all for my silly question.

Astounding
PROC Star

There are only two data types in SAS:  character and numeric.  So that error messing is confirming that ZIP is a character variable.  If you want the items in your IN list to be character, you need to put them in quotes:

 


and x.zip in ("28201", "28203", "28204", "28205")

Commas are optional, and macro language has nothing to do with it.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

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
  • 8 replies
  • 1235 views
  • 2 likes
  • 5 in conversation