How to use a hash table when a key value is missing

[SASsy05]

Hi,

I am trying to use hash tables to attach a numeric value when a certain drug name is found.

I have 2 tables -

     Table 1: drug_name_1, drug_name_2, drug_name_3 and drug_cid; some records only have drug_name_1 populated, some have drug_name_1 and drug_name_2, and some have all three. All records have drug_cid.

     Table 2: x_drug_name, drug_concept_id, (and some others not important to this at the moment). I am trying to find the ones that have drug_concept_id=0 and match the text terms in x_drug_name to the terms in Table 1 and assign a drug_cid to this record.

This is what I want to do:

1) Match if drug_name_1, drug_name_2 AND drug_name_3 are present in the field x_drug_name and assign drug_cid.

2) Match if drug_name_1 AND drug_name_2 are present in the field x_drug_name and assign drug_cid.

3) Match if only drug_name_1 is present in the field x_drug_name and assign drug_cid.

The below code is me attempting #1 above. I was going to run through this 3 times each with one less drug_name and removing the ones that matched at each step from each subsequent run. I think it is having problems because some of the records are missing drug_name_2 and/or drug_name_3. I am restricted to Table 1 being set up this way.

Any ideas on how to make this work?

Thanks for any and all suggestions!

data MatchCombo_a;
    length 	x_drug_name $100
			drug_name_1 $50
			drug_name_2 $50
			drug_name_3 $50; 

	If (_n_=1) then do;
	declare hash Drug_c (dataset: 'Drugs.med_statin');
	Drug_c.DefineKey ('drug_name_1', 'drug_name_2', 'drug_name_3'); 
	Drug_c.DefineData ('drug_name_1', 'drug_name_2','drug_name_3', 'drug_cid'); 
	Drug_c.DefineDone ();
 	call missing(drug_name_1, drug_name_2, drug_name_3, drug_cid); 
	end;

    do until (eof1) ;
    set sample.de_sample end = eof1;  
      where  drug_concept_id=0;
	  x_drug_name2=x_drug_name;
	  x_drug_name3=x_drug_name;

      do i = 1 to 5;  
	    drug_name_1=scan(upcase(x_drug_name),i,' -/!*#\,'); 

      do j = 1 to 5; 
		drug_name_2=scan(upcase(x_drug_name2),j,' -/!*#\,'); 

	  do k = 1 to 5;
		drug_name_3=scan(upcase(x_drug_name3),k,' -/!*#\,'); 

        rc = Drug_c.find(key: drug_name_1, key: drug_name_2, key: drug_name_3);
        if rc=0 then output;
		
	  End;
	  End;
	  End;
	End;

[SASsy05 · Accepted solution]

Never mind! Got it!

Just added a WHERE statement to the hash declare statment.

    Merge_3.DefineData ('drug_exposure_id', 'drug_exposure_start_date', 'x_drug_name', 'drug_concept_id', 'drug_cid');

Ran through the code three times.