%Let with Zip Codes

[triunk]

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

[Tom]

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]

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.

[DanielLangley]

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]

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]

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]

---

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

[triunk]

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

[Astounding · Accepted solution]

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.