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
data want;
set have;
if findc(strip(desc),strip(product2)) then product3=product2;
else product3=product;
run;
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;
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?
Hi,
Above suggested solutions are based on your sample data. Provide a sample data that reflects the current scenario.
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
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
FIND is not returning correct result. Its returning for first 2 product3 as A,A. I want it A,B
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
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.