Creating a 4th variable based on information from 3 variables

[munitech4u]

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

[stat_sas]

data want;

set have;

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

else product3=product;

run;

[user24feb]

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]

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]

Hi,

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

[munitech4u]

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]

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]

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

[Karthikeyan]

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

Thanks

[munitech4u]

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]

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.