BookmarkSubscribeRSS Feed
munitech4u
Quartz | Level 8

I have a dataset like this:

ID product  DESC       product2

1   A         A/B-Q   A

1   A         A/B-Q   B

2   A         XYZ      A

2   A         XYZ      B

I want to create the variable product3, such that it looks the product2 in variable "desc" and if its found then product3=product2 else product3=product. So product 3 will be:

Product3

A

B

A

A

10 REPLIES 10
stat_sas
Ammonite | Level 13

data want;

set have;

if findc(strip(desc),strip(product2)) then product3=product2;

else product3=product;

run;

user24feb
Barite | Level 11

Maybe proc sql:

Proc SQL;

  Create Table Want As

  Select *,

         Case When Index(Desc,Strip(product2)) Then product2 Else product End As product3

  From Have;

Quit;

munitech4u
Quartz | Level 8

Thank you,but this doesnt seem to be working in conditions where like i have product2 as say "Aol" and desc as "Kypro 60 MG". as product2 is not found n desc, the product3 should be equal to product.Also is it possible to look for only first 3 chars from the product2 in desc?

stat_sas
Ammonite | Level 13

Hi,

Above suggested solutions are based on your sample data. Provide a sample data that reflects the current scenario.

munitech4u
Quartz | Level 8

ID product  DESC       product2

1   A         A/B-Q            A

1   A         A/B-Q            B

2   A         XYZ               A

2   A         XYZ               B

3   AOL     TYF NA         All other

3   PED     PEDTRI/ORIG-TIM/QUOTE  PED

Output:

Product3

A

B

A

A

AOL

PED

I think this covers most of the cases that I have in data

Karthikeyan
Fluorite | Level 6

Hi

Use the function Find in the above code instead of Findc

data want;

set have;

if find(strip(desc),strip(product2)) then product3=product2;

else product3=product;

run;

FINDC will look for a character whereas FIND will look for the occurrence of a  string.

Thanks

munitech4u
Quartz | Level 8

FIND is not returning correct result. Its returning for first 2 product3 as A,A. I want it A,B

Karthikeyan
Fluorite | Level 6

Hi

I ran this code

data

have

;

input id product $ desc : $25. product2 $ ;

datalines;

1   A         A/B-Q            A

1   A         A/B-Q            B

2   A         XYZ               A

2   A         XYZ               B

3   AOL     TYF NA         All other

3   PED     PEDTRI/ORIG-TIM/QUOTE  PED

;

run;

data want;

set have;

if find(strip(desc),strip(product2)) then product3=product2;

else product3=product;

run;

proc print data = want;run;

and this is the output I got

Capture.PNG

Thanks

munitech4u
Quartz | Level 8

Hmm.. Actually thats not the exact data i have, i had tried to create sample data. For some reason its returning me results like A,A. But thanks for your help, i need to find out more.

user24feb
Barite | Level 11

Just a thought: In contrast to index, find allows for modifiers, this is you can trim your variables and start at a certain position forwards or backwards: find(desc,product2,'t',-3) should trim and starts backwards at position 3.


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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 10 replies
  • 1116 views
  • 1 like
  • 4 in conversation