Help using Base SAS procedures

create a proc sql without the need for multiple proc sqls

Accepted Solution Solved
Reply
Regular Contributor
Regular Contributor
Posts: 238
Accepted Solution

create a proc sql without the need for multiple proc sqls

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_NBRproc1proc2proc3proc4proc5proc6proc7proc8proc9proc10proc11proc12proc13proc14proc15proc16proc17proc18proc19proc20
1111190099212 11111 22222
2228227099214 11900 33333
333170001700399213 11111 44444
4449637299213 22222 55555
5559201292083 22222
666170001700399213 33333
7779083699215 44444
8889200492081 55555
9999083699215 66666
1219201492083 77777
1313641099214 88888
1419201292083 99999
1519201292083
1619201492083
171G0008Q2036
DATA HAVE 2ND
proc1proc2
G000899999
G0008Q2036
G000892083
G000833333
1700017003
1700099213
9083699215
9637299213

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


Accepted Solutions
Solution
‎01-29-2014 03:41 PM
Super User
Posts: 19,869

Re: create a proc sql without the need for multiple proc sqls

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;

View solution in original post


All Replies
Super User
Posts: 19,869

Re: create a proc sql without the need for multiple proc sqls

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?

Regular Contributor
Regular Contributor
Posts: 238

Re: create a proc sql without the need for multiple proc sqls

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

Solution
‎01-29-2014 03:41 PM
Super User
Posts: 19,869

Re: create a proc sql without the need for multiple proc sqls

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;

Respected Advisor
Posts: 4,934

Re: create a proc sql without the need for multiple proc sqls

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

    PG
    🔒 This topic is solved and locked.

    Need further help from the community? Please ask a new question.

    Discussion stats
    • 4 replies
    • 183 views
    • 0 likes
    • 3 in conversation