In( range)

[deleted_user]

Hi,

I'd like to be able to able to specify a range in an in( ) statement in a where clause.

%if &category. eq 'all' %then %let spids=1:30;
proc freq data=mydata;
tables scorecard;
where spid1 in(&spids.);
run;

It doesn't like the : to specify a range.
There may be many categories with a variety of ranges so I dont want to 1,2,3 all of them.

Any suggestions?

I've not been doing SAS for long but i'm keen to learn so please don't worry about teaching me to suck eggs. 😉

[LinusH]

... %let spids=1 and 30;
..
where spid1 between &SPIDS.;
...

/Linus

[deleted_user]

Yes, I'd thought of that, but many of the categories will be lists of non-sequential numbers so I really want to use in( ).

Thanks though.

For now I've written lists out longhand, but I'll need to do this a lot so ranges would be better.

I could macroise (if that's a word) the query so define the query at the top (different for each category) and then just call the query macro further down. Bit messy though. Using simple ranges in in( ) seems like it would be very useful and people I've been asking here all want me to report back if i can get it working. 😉

[sbb]

Consider the use of a numeric SAS format, which can be setup to handle ranges and explicit values. The SAS FORMAT procedure can be used to generate/maintain your values, either as instream code using a VALUE statement to build the format, or by using an external data file with the ranges / values, and then use a DATA step to read up the external file, build a SAS file suitable for use with PROC FORMAT and CNTLIN= to generate the format. Then you would use a PUT function in your WHERE statement rather than specifying a range.

Have a look at the SAS support website resources http://support.sas.com/ for references to PROC FORMAT, and my recommended approach which is to use CNTLIN=.


Scott Barry
SBBWorks, Inc.

[LinusH]

Just remember thai if you data is indexed on your "spid" is indexed or your data reside in an external RDBMS, you don't want any function calls in you where-clause. Use Scott's suggestion with %let, %sysfunc and putn function prior to your query (like in your initial example).

Regards,
Linus