create a proc sql without the need for multiple proc sqls

[tmm]

I posted this but the responder did not understand what I need so I will try explaining again. I have 1 main table that has claim and then proc1 through proc20 all in their own columns. Each claim has only 1 row with whatever procedures were performed in 1 row. I have a second table that we get from Medicare and that table says if proc1 is the first procedure and proc2 is the second procedure, we will not approve the claim. It will be denied. So, I am trying to compare our data to their data. The pasted is the 2 tables I have. I have to compare a.proc1 = b.proc1 and a.proc2=b.proc2..............but then I have to do the next series of procedures in the mix. So that is a.proc1=b.proc1 and a.proc3 = b.proc2.........This goes on until I get all the way to proc20. Then I have to start again and begin with the main table on the procedure in proc2. So that ends up a.proc2=b.proc1 and a.proc3=b.proc2. Then I would need to go all the way until I get to proc20. Then begin with the procedure in proc3 and so on. This creates 21 if my count is right, proc sql's per position, for a total of 420 proc sql;s that I then have to merge in the end. Seems a bit ridiculous. Ok, here is my data have and then an example of what I did for code so far to help with understanding. The prior responder was not sure and suggested left joins but can I left join on the same 2ndary table? Will try it:

DATA HAVE MAIN
CLM_AUD_NBR	proc1	proc2	proc3	proc4	proc5	proc6	proc7	proc8	proc9	proc10	proc11	proc12	proc13	proc14	proc15	proc16	proc17	proc18	proc19	proc20
111	11900	99212		11111													22222
222	82270	99214			11900													33333
333	17000	17003	99213				11111												44444
444	96372	99213							22222											55555
555	92012	92083						22222
666	17000	17003	99213							33333
777	90836	99215									44444
888	92004	92081										55555
999	90836	99215											66666
121	92014	92083												77777
131	36410	99214													88888
141	92012	92083														99999
151	92012	92083
161	92014	92083
171	G0008	Q2036
DATA HAVE 2ND
proc1	proc2
G0008	99999
G0008	Q2036
G0008	92083
G0008	33333
17000	17003
17000	99213
90836	99215
96372	99213

proc sql;

create table p1 as

(select a.*,

1. b.proc1 as tbl1_proc1,
2. b.proc2 as tbl1_proc2,
3. b.edit

from

phyclms a

inner join cci0 b

on a.proc1=b.proc1 and a.proc2=b.proc2

group by a.clm_aud_nbr);

quit;

proc sql;

create table cci.p2 as

(select a.*,

1. b.proc1 as tbl1_proc1,
2. b.proc2 as tbl1_proc2,
3. b.edit

from

phyclms a

inner join cci0 b

on a.proc1=b.proc1 and a.proc3=b.proc2

group by a.clm_aud_nbr);

quit;

proc sql;

create table cci.p3 as

(select a.*,

1. b.proc1 as tbl1_proc1,
2. b.proc2 as tbl1_proc2,
3. b.edit

from

phyclms a

inner join cci0 b

on a.proc1=b.proc1 and a.proc4=b.proc2

group by a.clm_aud_nbr);

quit;

The 3 above queries are different as you can see. They are not the same. p1 looks at a.proc2 = b.proc2......p2 looks at a.proc3 = b.proc2.........p3 looks at a.proc4 = b.proc2

[Reeza]

tmm wrote:

I have 1 main table that has claim and then proc1 through proc20 all in their own columns. Each claim has only 1 row with whatever procedures were performed in 1 row.
I have a second table that we get from Medicare and that table says if proc1 is the first procedure and proc2 is the second procedure, we will not approve the claim. It will be denied. So, I am trying to compare our data to their data. 

Are you trying to say that the first procedure must match the first procedure and the second procedure can be any of the next 19 procedures?

[tmm]

Yes. And now that you just put it that way, I think I can do the following.............a.proc1=b.proc1 and a.proc2=b.proc2 or a.proc3=b.proc2 or a.proc4=b.proc2 etc

[Reeza · Accepted solution]

Then you can use a single query as follows. If you need to determine which procedure matches I'd use a second step afterwards.

proc sql;

create table p1 as

(select a.*,

     b.proc1 as tbl1_proc1,

     b.proc2 as tbl1_proc2,

     b.edit

from

phyclms a

inner join cci0 b

on a.proc1=b.proc1 and (

a.proc2=b.proc2

OR a.proc2=b.proc3

OR a.proc2=b.proc4

etc.

)

group by a.clm_aud_nbr);

quit;

[PGStats]

Only a single query needed for this :

DATA HAVE;

infile datalines missover;

input CLM_AUD_NBR $ (proc1-proc20) ($);

array p

proc:;

do procNo = 1 to dim(p);

     proc = p[procNo];

     if not missing(proc) then output;

     end;

keep clm_aud_nbr procNo proc;

datalines;

111 11900 99212 . 11111 . . . . . . . . . . . . 22222 . .

222 82270 99214 . . 11900 . . . . . . . . . . . . 33333 .

333 17000 17003 99213 . . . 11111 . . . . . . . . . . . 44444

444 96372 99213 . . . . . . 22222 . . . . . . . . . . 55555

555 92012 92083 . . . . . 22222 . . . . . . . . . . .

666 17000 17003 99213 . . . . . . 33333 . . . . . . . . .

777 90836 99215 . . . . . . . . 44444 . . . . . . . .

888 92004 92081 . . . . . . . . . 55555 . . . . . . .

999 90836 99215 . . . . . . . . . . 66666 . . . . . .

121 92014 92083 . . . . . . . . . . . 77777 . . . . .

131 36410 99214 . . . . . . . . . . . . 88888 . . . .

141 92012 92083 . . . . . . . . . . . . . 99999 . . .

151 92012 92083 . . . . . . . . . . . . . . . . .

161 92014 92083 . . . . . . . . . . . . . . . . .

171 G0008 Q2036 . .

;

DATA HAVE2;

input procFirst $ procScnd $;

datalines;

G0008 99999

G0008 Q2036

G0008 92083

G0008 33333

17000 17003

17000 99213

90836 99215

96372 99213

;

proc sql;

create table deniedClaims as

select distinct hf.clm_aud_nbr

from

     have as hf inner join

     have as hs on hf.clm_aud_nbr=hs.clm_aud_nbr and hf.procNo<hs.procNo inner join

     have2 as h2 on hf.proc=h2.procFirst and hs.proc=h2.procScnd;

select * from deniedClaims;

quit;

PG