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.*,
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.*,
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.*,
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
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;
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?
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
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;
Only a single query needed for this :
DATA HAVE;
infile datalines missover;
input CLM_AUD_NBR $ (proc1-proc20) ($);
array p
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
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.