Like with In statement

[tmm]

I have this code and do not think it is 100% correct:

proc sql;

create table implants.test1 as

(select *

from implants.alldrgs_asofi

where clm_aud_nbr in ('%1111111111%', '%2222222222%', '%3333333333%', '%4444444444%', '%5555555555%', '%6666666666%',

'%7777777777%', '%8888888888%', '%9999999999%')

group by clm_aud_nbr);

run;

The numbers in the in are just made up since I cannot give you actual claim numbers due to privacy laws. But each claim is actually 12 digits in length and the 01, 02, 03 etc that follows the 10 digit claim number notes pages. We do not know how many pages are associated with each claim so all we want to do is pull all the pages associated with that 10 digit claim number without trying to figure out who has 01, 02, 03 etc. They can go on up to 50 pages in length.

[Reeza]

You can't use the like and the In operator together like that, what you can do is substring the clm_aud_nbr to 10 digits and check if it matches those in your list.

I don't think you need to group by, but that depends on what you need in the end.

proc sql;

create table implants.test1 as

(select *

from implants.alldrgs_asofi

where substr(clm_aud_nbr, 1, 10) in ('1111111111', '2222222222', '3333333333', '4444444444', '5555555555', '6666666666',

'7777777777', '8888888888', '9999999999')

group by clm_aud_nbr);

run;

If your ID's are in another list you can simplify it by checking if the clm_aud_nbr is in that list against the substr one.

proc sql;

create table implants.test1 as

(select *

from implants.alldrgs_asofi

where substr(clm_aud_nbr, 1, 10) in (select ids from other_table)

group by clm_aud_nbr);

run;

[tmm]

Yeah I know about substr but they want to see the 01, 02, 03's etc on each of the 10 claims. I guess I will have to do a like and then 1 claim at a time

[Reeza]

Try it.

This is a select * so selects the record, not the substr and only uses the substr to MATCH.

[tmm]

and now I am running 10 like queries with each of the claim numbers. I just had to do it that way cuz they want to see all the 01, 02, 03 etc on up to 50 if they have that many pages

[Reeza]

Did you try it or just assumed it didn't work?

[tmm]

I tried it and it gave me all the claims but it did not give me the 2 digits on the end to make the claim 12 digits in length. With the 1,10 it chops off those last 2 digits that are the page numbers for the 10 digit claim. That is ok. I just did 10 like queries and 1 of the 10 digit claims per and it is on number 6 running now. I am under a time constraint. This was due an hour and a half ago so I really don't have time to research or read any books or anything else. So I did the fastest, easiest thing I knew. I will then export it all and put it in Excel and just apologize. This project was thrown on me the last minute as things usually are and they said need it by 6pm est. Always the case.

[MikeZdeb]

hi ... another idea ...

proc format;

value $claim

'1111111111', '2222222222', '3333333333', '4444444444',

'5555555555', '6666666666', '7777777777', '8888888888', '9999999999' = '1'

other= '0';

run;

proc sql;

create table test1 as

select *

from alldrgs_asofi

where put(put(clm_aud_nbr,$10.),$claim.) eq '1'

order clm_aud_nbr;

run;

[Ksharp]

Or you can try to use CONTAINS operator.

Code not tested.

data have;
input id $20.;
cards;
1111111
2222222
3333333
4444444
;
run;


proc sql;
create table test1 as
select *
from alldrgs_asofi ,have
where clm_aud_nbr contains strip(id)
group by clm_aud_nbr;
quit;

Ksharp

[ballardw]

Looks like the other part of the code that is missing is to select the last two digits from the claims to get the page numbers, again using substr(claim, 11,2).

One part to select the record, the other to select the information

BTW I would say who ever devised that coding scheme should be beaten with a wet noodle. Claim numbers should be the claim and the number of pages should be another field

[tmm]

That is so true. But I did it with 10 separate statements to get it done. They don't care how it is done as long as it is done. Got to love execs. Yeah our claim system is somewhat messy. We have far too many of them as well. Too many entities acquired in such a short period of time and we have all these ODBC and DB2 connection strings to connect to the various servers and DB's and all have different data dictionaries and ways of doing things. It does create a mess for us programmers and I am kind of new to SAS.