Matching a table with two possible look-up tables

[DocMartin]

Hospitalized patients can have a diagnosis coded in their medical record in one of two ways: as an APRDRG or an MSDRG.  These are stored in two look-up tables “APR” and ‘MS”.  

* Tables apr and ms;

data apr; input diagnosis $21. aprdrg $3.;
cards;
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; input  diagnosis $21.  msdrg $3.;
cards;
Heart_Failure           001
Myocardial_Infarction   005
Stroke	                098
Respiratory_Failure	    103
GI_Bleeding	            112
GI_Cancer	            
Asthma                  174
Influenza	            298
Head_Trauma	            300
Pelvic_Fractue          302
;
run;

I also have a table of patients called "MAIN", in which patients have an ID, code, and drg_type to let you know that the diagnosis should be taken from the corresponding table (apr or ms).

/* Sometimes the diagnosis for an aprdrg is the same as that for an msdrg
   But most of the time it isn't. A few diagnoses appear in one table but 
   not the other. Both tables have diagnoses with missing aprdrg or msdrg. */

* Table MAIN with PTID (patient ID), code(string that matches up with APRDRG & MSDRG values).;

data main; input @1 ptid $3.  @5 code $3. @9 drg_type $1.;
cards;
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;  

What I want is to get a final table that contains each patient and their correct diagnosis. It would be easy if the APR and MS DRG codes were distinct. But some diagnoses have both an APRDRG and an MSDRG code, and they could be different. The way around it for each patient is to determine if the drg_type is A then see if code matches up with an APRDRG code. If so, then grab the corresponding diagnosis from the APR table. If not, then use the MS table. If neither work, then leave a blank for diagnosis. The same goes if a code is M. Look first in the MS table, then the APR table, if no match then use the MS table, and finally leave a blank if neither matches up. The final table looks like this:

* drg_type in rthis table indicates which look-up table provided the diagnosis.
data final; input PatientID $3.	drg_type $1. code $3.Diagnosis $21.;
cards;
111	A 002  Myocardial_Infarction
112	A 103  Stroke
113	M 112  GI_Bleeding
114	A 298  Influenza
115	A 300  Head_Trauma
116	M 005  Myocardial_Infarction
117	M 103  Respiratory_Failure
118	M 112  GI_Bleeding
119	A 167  GI_Cancer
120	'' 222  ''
;	

 I'm uncertain how to do this, so any help would be much appreciated.

Thanks!

[novinosrin]

data main; 
input @1 ptid $3.  @5 code $3. @9 drg_type $1.;
cards;
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; 
infile cards truncover;
input diagnosis $21. aprdrg :$3.;
cards;
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 cards truncover;
input  diagnosis $21.  msdrg :$3.;
cards;
Heart_Failure           001
Myocardial_Infarction   005
Stroke	                098
Respiratory_Failure	    103
GI_Bleeding	            112
GI_Cancer	            
Asthma                  174
Influenza	            298
Head_Trauma	            300
Pelvic_Fractue          302
;
run;


proc sql;
  create table want as
  select  ptid ,	drg_type , code , 
  case when drg_type='A' then coalesce(b.diagnosis,c.diagnosis)
     when drg_type='M' then coalesce(c.diagnosis,b.diagnosis)
	 else  ' ' end as diagnosis
  from main a
  left join apr b
  on a.code=b.aprdrg
  left join ms c
  on a.code=c.msdrg
  order by ptid; 
quit;

[DocMartin]

This is a concise way of doing what I wanted. So thank you! But there's one item yet to be resolved: the final table's drg_type should be the table that produced the match. In most cases this will be the original drg_type, but not always.

Note how below patient 113 has a drg_type of 'M' and patient 119 has a drg_type of 'A"'. These represent which look-up table the match was made in, not necessarily the drg_type in data set MAIN. Patient 120's code of 222 was not i either table, so his drg_type should be blank.

data final; input PatientID $3.	drg_type $1. code @11.Diagnosis $21.;
cards;
111	A 002  Myocardial_Infarction
112	A 103  Stroke
113	M 112  GI_Bleeding
114	A 298  Influenza
115	A 300  Head_Trauma
116	M 005  Myocardial_Infarction
117	M 103  Respiratory_Failure
118	M 112  GI_Bleeding
119	A 167  GI_Cancer
120	  222   
;	

[novinosrin · Accepted solution]

Hi @DocMartin  Keeping it simple-

proc sql;
	create table want1 as
		select  ptid , 
			case 
				when drg_type='A' and   b.diagnosis>' ' then 'A'
				when drg_type='A' and c.diagnosis>' ' then 'M' 
				when drg_type='M' and   c.diagnosis>' ' then 'M'
				when drg_type='M' and b.diagnosis>' ' then 'A' 
				else ' ' 
			end as drg_type , 
			code , 
		case 
			when drg_type='A' then coalesce(b.diagnosis,c.diagnosis)
			when drg_type='M' then coalesce(c.diagnosis,b.diagnosis)
			else  ' ' 
		end as diagnosis
		from main a
			left join apr b
				on a.code=b.aprdrg
			left join ms c
				on a.code=c.msdrg
			order by ptid;
quit;

[DocMartin]

Thanks!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[Oligolas]

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;

[andreas_lds]

I am stuck at

The way around it for each patient is to determine if the drg_type

If that variable does not exist in the originals data source, how to decide in which look-up table to look first?

The following solution assumes that that drg_type exists:

data work.apr_fmt;
    set work.apr(rename=(aprdrg = Start diagnosis = label));    
    where not missing(Start);
    
    retain FmtName "$diag_a";
run;
    
data work.ms_fmt;
    set work.ms(rename=(msdrg = Start diagnosis = label));    
    where not missing(Start);
    
    retain FmtName "$diag_m";
run;
    
proc format cntlin=work.apr_fmt;
run;

proc format cntlin=work.ms_fmt;
run;


data want;
    set main;
    
    length fmtname $ 10 diagnosis $ 30;
    
    fmtname = cats('$diag_', drg_type, '.');
    diagnosis = putc(code, fmtname);
run;

[Kurt_Bremser]

Multiple and/or conditional lookups are the domain of hash objects:

data apr;
infile datalines truncover;
input diagnosis :$21. aprdrg :$3.;
cards;
Heart_Failure	        001
Myocardial_Infarction   002
Stroke	                103
Respiratory_Failure	    
GI_Bleeding	            116
GI_Cancer	            167
GI_Obstruction          172
Influenza	            298
Head_Trauma	            300
;

data ms;
infile datalines truncover;
input diagnosis :$21.  msdrg :$3.;
cards;
Heart_Failure           001
Myocardial_Infarction   005
Stroke	                098
Respiratory_Failure	    103
GI_Bleeding	            112
GI_Cancer	            
Asthma                  174
Influenza	            298
Head_Trauma	            300
Pelvic_Fractue          302
;

data main;
input @1 ptid $3.  @5 code $3. @9 drg_type $1.;
cards;
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
;

data want;
set main;
length diagnosis $21;
if _n_ = 1
then  do;
  declare hash apr (dataset:"apr (rename=(aprdrg=code))");
  apr.definekey("code");
  apr.definedata("diagnosis");
  apr.definedone();
  declare hash ms (dataset:"ms (rename=(msdrg=code))");
  ms.definekey("code");
  ms.definedata("diagnosis");
  ms.definedone();
end;
select (drg_type);
  when ("A") rc = apr.find();
  when ("M") rc = ms.find();
  otherwise diagnosis = "not found";
end;
if rc ne 0 then diagnosis = "not found";
drop rc;
run;