DATA Step, Macro, Functions and more

wildcards for where and if

Reply
Contributor
Posts: 33

wildcards for where and if

[ Edited ]

 

Hi,

I have a table of drugs, and i need to update the generic name for each drugs.

for example:

Colum1 (Drug)                Column2(Generic name) 

RECLIMET                     Metformin            (-----> i do have update 'METFORMIN' in Col2)

........                                .......

Reclument                      .......

Relument                        .......

 

 

 

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 

 

proc sql;

update table_name

set Column2 = 'METFORMIN'
where ddrug11 =* 'RECLIMET';
run;

 

proc sql;

update table_name

set Column2 = 'ASPIRIN'
where ddrug11 =* 'ECOSPRIN';
run;

 

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.

Thank you,

Super User
Super User
Posts: 9,599

Re: wildcards for where and if

Posted in reply to Sathish_jammy

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:

https://en.wikipedia.org/wiki/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.

Super User
Posts: 10,280

Re: wildcards for where and if

[ Edited ]
Posted in reply to Sathish_jammy

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.

 

Edit:

As an alternative, create a format from your wrong/right pairs, and apply that in a single put() function call.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Ask a Question
Discussion stats
  • 2 replies
  • 84 views
  • 0 likes
  • 3 in conversation