topic wildcards for where and if in SAS Programming

wildcards for where and if

Sathish_jammy — Mon, 18 Jun 2018 08:16:33 GMT

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,

Re: wildcards for where and if

RW9 — Mon, 18 Jun 2018 07:52:38 GMT

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.

Re: wildcards for where and if

Kurt_Bremser — Mon, 18 Jun 2018 07:56:05 GMT

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.