BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SASsy05
Obsidian | Level 7

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;
1 ACCEPTED SOLUTION

Accepted Solutions
SASsy05
Obsidian | Level 7

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

1 REPLY 1
SASsy05
Obsidian | Level 7

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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