topic Re: Can I use specific numbers series in a where statement? in New SAS User

Can I use specific numbers series in a where statement?

Sandeep77 — Thu, 09 Feb 2023 10:37:41 GMT

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

Re: Can I use specific numbers series in a where statement?

ballardw — Thu, 09 Feb 2023 11:03:45 GMT

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.

Re: Can I use specific numbers series in a where statement?

Sandeep77 — Thu, 09 Feb 2023 11:17:43 GMT

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

Re: Can I use specific numbers series in a where statement?

ballardw — Thu, 09 Feb 2023 11:20:16 GMT

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