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
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;
Reeza,
Not sure if you really want SQL code ?
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
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.