Can I use specific numbers series in a where statement?

[Sandeep77]

Hi all,

I am using a code where I have used a where statement like  where t1.client_code in ('LBG001','LBG002','LBG003');. I want to write a similar code but the client_code is from LBG004 to LBG042. So I want to know is there any option in SAS to do this or I have to write manually from 004 to 042?

Thanks

[ballardw · Accepted solution]

With your " t1.client_code" I have to assume that you are using this where in Proc SQL. SQL in general is very verbose. So the may not be a way around this. 

There is chance, slim and be extremely careful with this, if all of the values of interest are sequential that you could use

 where t1.client_code  between 'LBG001' and 'LBG003'

This will not yield expected results if the length of the values changes  due to the way character values are compared:

data example;
   input code $;
datalines;
LBG001
LBG002
LBG003
LBG004
LBG007
LBG00222
LBG000
LBG00
LB
;

proc sql;
   create table want as
   select *
   from example
   where code between 'LBG001' and 'LBG003'
   ;
quit;

Because the values of the between range have 6 characters the longer LBG00222 stops comparing at the first 2 and is "between" the range limits even though the second 2 would make a person think it doesn't fit. It may be possible to provide other restrictions in the Where, such as "and length(code)=6" but the particulars of each set will make this a potential exercise in making headaches.

[Sandeep77]

Yes, it was in sequence and the between statement worked. Thank you!

[ballardw]

---

@Sandeep77 wrote:

Yes, it was in sequence and the between statement worked. Thank you!

---

I might suggest a test of creating data set of the distinct values of the code and testing on that reduced set just in case. I do not like to consider the number of times I have been given a list of "all the values" only to have others actually appear in the data. Character values especially have issues with mixed case.