topic Re: %Let with Zip Codes in SAS Programming

%Let with Zip Codes

triunk — Thu, 10 Jan 2019 18:52:24 GMT

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

Re: %Let with Zip Codes

Tom — Thu, 10 Jan 2019 18:56:10 GMT

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.

Re: %Let with Zip Codes

PaigeMiller — Thu, 10 Jan 2019 18:58:30 GMT

Two key points here:

numbers are text in the macro processor
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.

Re: %Let with Zip Codes

DanielLangley — Thu, 10 Jan 2019 18:59:25 GMT

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.

Re: %Let with Zip Codes

Tom — Thu, 10 Jan 2019 19:06:21 GMT

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.

Re: %Let with Zip Codes

triunk — Thu, 10 Jan 2019 19:13:07 GMT

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"

Re: %Let with Zip Codes

PaigeMiller — Thu, 10 Jan 2019 19:15:04 GMT

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

Re: %Let with Zip Codes

triunk — Thu, 10 Jan 2019 19:15:46 GMT

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

Re: %Let with Zip Codes

Astounding — Thu, 10 Jan 2019 19:15:55 GMT

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.