DATA Step, Macro, Functions and more

How do I match on two key variables in hash

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

How do I match on two key variables in hash

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


Accepted Solutions
Solution
‎11-14-2016 08:59 PM
Valued Guide
Posts: 797

Re: How do I match on two key variables in hash

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

View solution in original post


All Replies
Valued Guide
Posts: 797

Re: How do I match on two key variables in hash

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.
Occasional Contributor
Posts: 13

Re: How do I match on two key variables in hash

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.

Solution
‎11-14-2016 08:59 PM
Valued Guide
Posts: 797

Re: How do I match on two key variables in hash

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
Occasional Contributor
Posts: 13

Re: How do I match on two key variables in hash

Thank you! That did the trick!

 

I probably should have asked the question 8 hrs ago Smiley Happy

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 242 views
  • 0 likes
  • 2 in conversation