How do I change values of a variable by values from a different dataset (trade name to generic)

[Talat]

I have a data set where patients have stated the medicine they took. I need to convert the medicine names to the generic name. Doing it by 'if..then' statement' would be too time-consuming.  There should be a simple Macro approach but got stuck. 

I have attached two datasets:

Tes datat~ has 8 variables: ID=cid.  med1-med7 (name of medicines as provided)

Allmeds~ 3 variables. 'Gname' is an index for all the med1-med7 names provided by the patients.  'Generic' is the generic name of the corresponding drug in 'Gname'.

I want to replace the med1-med7 in the Test dataset with the corresponding generic name from Allmeds data set. 

For example, 

the 28th subject stated the use of Paracetamol   Ambrox.

In the new dataset, it should be:  Acetominophen  Ambroxol

Thanks for your help.

-Talat

I have no problem if these are new variables (gmed1-gmed7).

[Patrick · Accepted solution]

You could use your allmed table to create a format. Then either just apply the format to the variables in test or create new variables using syntax like: genMed1=put(med1, <format name>);

Here the code for creating a format from a dataset like documented here.

data fmt_source;
  set allmed(keep= gname generic rename=(gname=start generic=label));
  retain fmtname '$medNameGen' type 'c';
run;

proc format cntlin=fmt_source;
run;

[Talat]

Thanks Patrick. It worked perfectly! Such simple codes!!

[mkeintz]

This program creates 7 new variables gmed1-gmed7 to correspond to your old variables.  If the old medicine is not blank, and is found in the allmed table, then the corresponding new variable will have the generic name.  But if the old variables is not matched in the allmed table the generic variable will have a blank value:

data want (drop=i gname generic);
  set test;
  if _n_=1 then do;
    if 0 then set allmed (keep=gname generic);
    declare hash h (dataset:'allmed (keep=gname generic)');
      h.definekey('gname');
      h.definedata('generic');
      h.definedone();
  end;
  array meds  {*}     med1-med7;
  array gmeds {*} $20 gmed1-gmed7;
  do i=1 to 7;
    if meds{i}=' ' then continue;
    if h.find(key:meds{i})=0 then gmeds{i}=generic;
  end;
run;

This simple lookup action is probably the most common usage of hash objects.

[Talat]

Thanks Keintz. This works too!