SAS Macro variable to represent what is in an IN statement in Proc SQL

0
votes

I have a query I want to run through SAS in Proc SQL where I am getting data from one of our company databases. At the top of the query, for ease of use mostly, I want to be able to put a list of input variables. I am interested in getting data only in certain dates and certain states. The dates I care about are contiguous so I just make a SAS macro variable for the start date and end date and use a between statement. That's easy enough. But, for the states, I can't do such a thing. So, my thought was to do something like

%LET States = ('CT', 'MD', 'ME', 'NC', 'WV');

and then later on, I want to do a where statement

WHERE (State_Tp IN &States)

Now, this does not work. And, I've tried several other variations but I can't seem to get it to work. Is something like this possible?

1
sqlsassas-macro
Your code works fine as is with a trivial example. What error are you getting? – Joe
And, I'm an idiot. The error never made sense to me but it always happened when I uncommented these lines so I assumed that didn't work (and I had a different error for different versions). But, I looked again because of your comment and I'm an idiot is a good description. I was pulling the numeric code from our company database instead of the 2 character state abbreviation. So, it was calling "A character string failed conversion to a numeric value." Sorry and thanks for the help. I think it works now. – GeoffDS

1 Answers

2
votes

While your code is fine as is, a better solution (that might have less issues anyway) would be to create a dataset with the desired states, and join against it (or use an exists clause if that is better for your needs). This is easier to maintain (as you can keep the dataset in an easily editable format separate from the code, like in excel) and may be faster in some cases.

data states;
input state_tp $;
datalines;
CT
MD
ME
NC
WV
;;;;
run;

proc sql;
create table test as
select Z.* 
 from sashelp.zipcode Z
 inner join
 states s 
 on z.state_tp=s.state_tp;
quit;

or

proc sql;
create table test as
select * from sashelp.zipcode z
where exists (
  select 1 from states s
   where s.state_tp=z.state_tp);
quit;