How do I match on two key variables in hash

[SASsy05]

Hello,

I have been working on the following for quite some time, and I just can't seem to figure it out.

I have 2 tables -

     Table 1: ingredient_1, ingredient_2 and drug_cid;

     Table 2: x_drug_name, drug_concept_id, (and some others not important to this at the moment)

I want to match ingredient_1 AND ingredient_2 from Table 1 to x_drug_name in Table 2 when drug_concept_id=0. I am doing a text matching. x_drug_name might look something like this:  "amLODIPine-atorvastatin 5 mg-20 mg oral tablet".

When there is a record where both ingredient_1 and ingredient_2 are present, I want to add drug_cid to the record.

Here is my code:

  data TxtMatch;
    length 	x_drug_name $ 50
			ingredient_1 $50
			ingredient_2 $50; 

	If (_n_=1) then do;
	declare hash drug (dataset: 'Drugs.statincombos');
	Drug.DefineKey ('ingredient_1', 'ingredient_2'); 
	Drug.DefineData ('ingredient_1', 'ingredient_2','drug_cid'); 
	Drug.DefineDone ();
 	call missing(ingredient_1, ingredient_2, drug_cid); 
	end;

    do until (eof2) ;
    set sample.de_sample2 end = eof2;  
      where  drug_concept_id=0;
	  x_drug_name2=x_drug_name;

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

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

        rc = Drug.find(key: 'ingredient_1', key: 'ingredient_2');
        if rc=0  then output; 
	  End;
	  End;

	End;

   keep drug_exposure_id
   		drug_exposure_start_date
        x_drug_name 
		x_drug_name2
		ingredient_1
        ingredient_2 
        drug_concept_id 
        drug_cid;

   
  run;

I am not getting any errors, so I am not sure where the problem is.

Thank you for any help and guidance you may be able to provide

--SS

[mkeintz]

Your using I to index the do loop for ingredient_1 and also ingredient_2. So ingredient_1 will get I=1 for the outer loop, then ingredient_2 will get I=1,2,3,4,5 for the inner loop. Now I=6, so when the outer loop is re-entered, I is already too high.

Use J for ingredient_2.

[SASsy05]

Thank you for the suggestion. I made the following change:

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

Unfortunately, it is still not working.

[mkeintz · Accepted solution]

I see it now.

Your key values to the drug.find() method have the var names in quotes. So you're using the literal values as the keys, NOT the values in the variables. Remove the quotes.

Regards,
Mark

[SASsy05]

Thank you! That did the trick!

I probably should have asked the question 8 hrs ago 🙂