topic Re: SQL Join Issues in SAS Procedures

SQL Join Issues

Reeza — Wed, 26 Aug 2015 17:55:42 GMT

My brain doesn't seem to want to process SQL today.

I have two tables that I need to merge and look up values from. There are two key variables, but if the variable is missing then ALL values are valid to join on, but if there is a record with a value then merge with that one instead.

In the table below the issue is with record 6 that needs to be merged with the last value in the lookup table rather than the second record in the look up table.

Any help appreciated

data claims;

infile cards dlm=',';

input ID Specialty $ Service_Code $ Units;

cards;

1, GP, 01.01A, 24

2, , 01.01A, 30

3, NEUR, 01.01A, 40

4, , 02.02A, 25

4, GP, 02.02A, 30

4, NEUR, 02.02A, 30

;

run;

data factor;

infile cards dlm=',';

input Service_Code $ Specialty $ Multiplier;

cards;

01.01A, , 2

02.02A, ,3

02.02A,NEUR,4

;

run;

Desired output:

Obs ID Specialty Code Units Multiplier

1 1 GP 01.01A 24 2

2 2 01.01A 30 2

3 3 NEUR 01.01A 40 2

4 4 02.02A 25 3

5 4 GP 02.02A 30 3

6 4 NEUR 02.02A 30 4

Re: SQL Join Issues

PGStats — Thu, 10 Sep 2015 03:18:59 GMT

I'm not sure I really understand. You seem to want missing specialties in table factor to match specialties in table claims that are absent from table factor. I would suggest this:

proc sql;
select
    c.id,
    c.specialty,
    c.service_code as code,
    c.units,
    coalesce(f.multiplier, _f.multiplier) as multiplier
from
    claims as c left join
    factor as f on c.Service_Code=f.Service_Code and c.specialty=f.specialty left join
    factor as _f on c.Service_Code=_f.Service_Code
where _f.specialty is missing
order by id, specialty;
quit;

Re: SQL Join Issues

Ksharp — Thu, 27 Aug 2015 12:03:41 GMT

Reeza,

Not sure if you really want SQL code ?

Code: Program

data claims;
infile cards dlm=',';
input ID  Specialty $ Service_Code $ Units;
cards;
1, GP, 01.01A, 24
2, , 01.01A, 30
3, NEUR, 01.01A, 40
4, , 02.02A, 25
4, GP, 02.02A, 30
4, NEUR, 02.02A, 30
;
run;
 
 
data factor;
infile cards dlm=',';
input Service_Code $ Specialty $ Multiplier;
cards;
01.01A, , 2
02.02A, ,3
02.02A,NEUR,4
;
run;

data temp;
 if _n_ eq 1 then do;
  if 0 then set factor;
  declare hash ha(dataset:'factor');
  ha.definekey('Service_Code','Specialty');
  ha.definedone();
 end;
 set claims;
 length key $ 40;
 if ha.check()=0 then key=Specialty ;
  else call missing(key);
drop Multiplier;
run;
data want;
 merge temp factor(rename=(Specialty=key));
 by Service_Code key;
 drop key;
run;

Xia Keshan