Hi,
I'd basically merge everything together and pick up the desired values from the requested table if available and if missing I'd look in the other one.
You could do this straight in one step but I chose to split the steps in order to add some kind of error detection
* Tables apr and ms;
data apr;
infile datalines TRUNCOVER;
input diagnosis :$25. aprdrg :$3.;
Datalines4;
Heart_Failure 001
Myocardial_Infarction 002
Stroke 103
Respiratory_Failure
GI_Bleeding 116
GI_Cancer 167
GI_Obstruction 172
Influenza 298
Head_Trauma 300
;;;;
run;
data ms;
infile datalines TRUNCOVER;
input diagnosis :$25. msdrg :$3.;
Datalines4;
Heart_Failure 001
Myocardial_Infarction 005
Stroke 098
Respiratory_Failure 103
GI_Bleeding 112
GI_Cancer
Asthma 174
Influenza 298
Head_Trauma 300
Pelvic_Fracture 302
;;;;
run;
* Table MAIN with PTID (patient ID), code(string that matches up with APRDRG & MSDRG values).;
data main;
infile datalines TRUNCOVER;
input ptid :$3. code :$3. drg_type :$1.;
Datalines4;
111 002 A
112 103 A
113 112 A
114 298 A
115 300 A
116 005 M
117 103 M
118 112 M
119 167 M
120 222 M
;;;;
run;
data _apr;
set apr(rename=(aprdrg=code));
run;
data _ms;
set ms(rename=(msdrg=code));
run;
*Merge data together;
PROC SQL;
CREATE TABLE _main AS
SELECT a.*,b.diagnosis as diagnosis_apr,b.code as code_apr,c.diagnosis as diagnosis_ms,c.code as code_ms
FROM main a
LEFT JOIN _apr b
ON a.code eq b.code
LEFT JOIN _ms c
ON a.code eq c.code
;
QUIT;
*Proceed selection of diagnosis - first in the referenced lookup table - second in the other one if values are missing;
DATA _want;
set _main;
length diagnosis $50;
if drg_type eq 'A' then do;
if cmiss(diagnosis_apr,code_apr) eq 0 then diagnosis=diagnosis_apr;
else if cmiss(diagnosis_ms,code_ms) eq 0 then diagnosis=diagnosis_ms;
else put 'W' 'ARNING: DRUG TYPE A code or diagnosis not found: ' ptid= code= drg_type= @100 diagnosis_apr= code_apr= diagnosis_ms= code_ms=;
end;
else if drg_type eq 'M' then do;
if cmiss(diagnosis_ms,code_ms) eq 0 then diagnosis=diagnosis_ms;
else if cmiss(diagnosis_apr,code_apr) eq 0 then diagnosis=diagnosis_apr;
else put 'W' 'ARNING: DRUG TYPE M code or diagnosis not found: ' ptid= code= drg_type= @100 diagnosis_apr= code_apr= diagnosis_ms= code_ms=;
end;
else put 'E' 'RROR: unknown type found ' ptid= code= drg_type= ;
RUN;
*Re-order the variables - select what is wanted and rename;
DATA want;
retain PatientID drg_type code diagnosis;*reorder;
set _want(rename=(ptid=PatientID) keep=ptid drg_type code diagnosis);*rename and select;
RUN;
PROC SORT DATA=want; BY PatientID drg_type code diagnosis; RUN;
*Delete tmp data;
PROC DATASETS lib=work nolist;delete _:;RUN;QUIT;
... View more
