## Creating a 4th variable based on information from 3 variables

Regular Contributor
Posts: 196

# Creating a 4th variable based on information from 3 variables

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

Posts: 1,270

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

data want;

set have;

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

else product3=product;

run;

Super Contributor
Posts: 355

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

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;

Regular Contributor
Posts: 196

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

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?

Posts: 1,270

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

Hi,

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

Regular Contributor
Posts: 196

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

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

Contributor
Posts: 37

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

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

Regular Contributor
Posts: 196

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

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

Contributor
Posts: 37

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

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

Regular Contributor
Posts: 196

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

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.

Super Contributor
Posts: 355