topic Re: Creating a 4th variable based on information from 3 variables in SAS Procedures

Creating a 4th variable based on information from 3 variables

munitech4u — Wed, 10 Dec 2014 14:06:19 GMT

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

Re: Creating a 4th variable based on information from 3 variables

stat_sas — Wed, 10 Dec 2014 14:16:51 GMT

data want;

set have;

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

else product3=product;

run;

Re: Creating a 4th variable based on information from 3 variables

user24feb — Wed, 10 Dec 2014 14:42:48 GMT

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;

Re: Creating a 4th variable based on information from 3 variables

munitech4u — Thu, 11 Dec 2014 05:14:30 GMT

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?

Re: Creating a 4th variable based on information from 3 variables

stat_sas — Thu, 11 Dec 2014 05:56:12 GMT

Hi,

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

Re: Creating a 4th variable based on information from 3 variables

munitech4u — Thu, 11 Dec 2014 06:03:07 GMT

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

AOL

PED

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

Re: Creating a 4th variable based on information from 3 variables

Karthikeyan — Thu, 11 Dec 2014 06:25:17 GMT

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

Re: Creating a 4th variable based on information from 3 variables

munitech4u — Thu, 11 Dec 2014 07:57:24 GMT

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

Re: Creating a 4th variable based on information from 3 variables

Karthikeyan — Thu, 11 Dec 2014 08:18:20 GMT

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

Re: Creating a 4th variable based on information from 3 variables

munitech4u — Thu, 11 Dec 2014 08:42:18 GMT

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.

Re: Creating a 4th variable based on information from 3 variables

user24feb — Thu, 11 Dec 2014 08:52:14 GMT

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.