BookmarkSubscribeRSS Feed
Sathish_jammy
Lapis Lazuli | Level 10

 

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,

2 REPLIES 2
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Kurt_Bremser
Super User

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.

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
  • 2 replies
  • 615 views
  • 0 likes
  • 3 in conversation