06-18-2018 03:20 AM - edited 06-18-2018 04:16 AM
I have a table of drugs, and i need to update the generic name for each drugs.
Colum1 (Drug) Column2(Generic name)
RECLIMET Metformin (-----> i do have update 'METFORMIN' in Col2)
In my table most of drugs have are typo error. like RECLIMET as RECLUMRT/RELIMET but i should find all those and update the correct name in Column2.
I tried with some stuffs like
set Column2 = 'METFORMIN'
where ddrug11 =* 'RECLIMET';
set Column2 = 'ASPIRIN'
where ddrug11 =* 'ECOSPRIN';
IS IT RIGHT?
----IF IT IS RIGHT IN THE SENSE, HOW SHOULD I UPDATE FOR ALL 1000 OF DRUGS
----IF IT IS WRONG GIVE ME A SOLUTION.
On the whole how could i solve all. Is there any way to come across this problem using macro or something else.
Even if u solve it in simple data steps are also fine for me.
What ever the method it is... Please suggest me.
Please help to resolve this.
06-18-2018 03:52 AM
This is drug coding. It is normally coded within the database or other tool by a specialist coder armed with coding libraries. It is really not a good idea for you to start doing this, you may think think the names are the same, but they may not be based on many different factors. Anyways, your risk, I wouldn't be doing it. If you have to do it, the most efficient way is to have the coding libraries - get the WHO-Drug Dictionary:
then merge that onto your data. You will need to apply some manual review where there are non-matches.
In response to the shouting: "IS IT RIGHT? IF IT IS RIGHT IN THE SENSE, THIS IS HOW SHOULD I UPDATE FOR ALL 1000 OF DRUGS "
The answer is NO ITS NOT THE RIGHT WAY, see above.
To this question:
"I just want to know which method is more effective wildcards in IF or WHERE
NOTE : not only in IF and WHERE, Is there any other method please let me know."
It depends on the scenario. If does not work in SQL for instance. What would be a better idea is to get a distinct list of values from your data, then apply some review to the smaller dataset, and set each medication correctly, then use this to merge back to your data - i.e. what you would do with the dictionary above.
06-18-2018 03:54 AM - edited 06-18-2018 03:56 AM
Start by creating a dataset that contains wrong and correct names:
data control; input wrong :$20. right :$20.; cards; RECLIMET Metformin ; run;
Then use that to dynamically write a data step that does all the corrections in one pass:
data _null_; set control end=eof; if _n_ = 1 then call execute('data want; set have;'); call execute('if ddrug11 = "' !! strip(wrong) !! '" then column2 = "' !! strip(right) !! '";'); if eof then call execute('run;'); run;
After that, you only need to expand the control dataset with new wrong/right pairs.
As an alternative, create a format from your wrong/right pairs, and apply that in a single put() function call.