SQL Join Issues

[Reeza]

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

[PGStats]

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;

[Ksharp]

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