Hello Everyone!
So i have a data set with a column named basket and the bastek is composed of the list of procuts a customer have separed by " / ":
customer_id basket
1 Product1 / Product2 / Product3
2 Product1
3 Product1 / Product2
I want to create flags for each product the customer have so the output would be something like:
customer_id Product1 Product2 Product3
1 1 1 1
2 1 0 0
3 1 1 0
Can anyone help me on this please? Thanks!
Try this
data have;
input customer_id basket $ 3-35;
datalines;
1 Product1 / Product2 / Product3
2 Product1
3 Product1 / Product2
;
data temp(keep = customer_id w d);
set have;
do c = 1 to countw(basket);
w = scan(basket, c, ' /');
d = 1;
output;
end;
run;
proc transpose data = temp out = want(drop = _:);
by customer_id;
id w;
var d;
run;
Can basket contain other strings than Product1, Product2 and Product3?
yes it can the product 1 2 and 3 are just example, actually they have names the products
Try this
data have;
input customer_id basket $ 3-35;
datalines;
1 Product1 / Product2 / Product3
2 Product1
3 Product1 / Product2
;
data temp(keep = customer_id w d);
set have;
do c = 1 to countw(basket);
w = scan(basket, c, ' /');
d = 1;
output;
end;
run;
proc transpose data = temp out = want(drop = _:);
by customer_id;
id w;
var d;
run;
Are your product names really going to be valid SAS variable names?
You probably will need to make a REPORT instead of a dataset. The dataset should look like:
customer_id product
1 Product1
1 Product2
1 Product3
2 Product1
3 Product1
3 Product2
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.