create flags on products based on a single column with multiple product names

[msf2021]

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!

[PeterClemmensen]

Can basket contain other strings than Product1, Product2 and Product3?

[msf2021]

yes it can the product 1 2 and 3 are just example, actually they have names the products 

[PeterClemmensen · Accepted solution]

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;

[Tom]

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