DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

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

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;

Accepted Solutions
Solution
‎11-28-2016 07:12 PM
Occasional Contributor
Posts: 13

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

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.

View solution in original post


All Replies
Solution
‎11-28-2016 07:12 PM
Occasional Contributor
Posts: 13

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

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.

☑ This topic is SOLVED.

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

Discussion stats
  • 1 reply
  • 143 views
  • 0 likes
  • 1 in conversation