topic create flags on products based on a single column with multiple product names in SAS Programming

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

msf2021 — Wed, 15 Jan 2025 18:27:50 GMT

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!

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

PeterClemmensen — Wed, 15 Jan 2025 18:35:34 GMT

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

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

msf2021 — Wed, 15 Jan 2025 18:37:13 GMT

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

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

PeterClemmensen — Wed, 15 Jan 2025 18:55:01 GMT

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;

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

Tom — Wed, 15 Jan 2025 19:08:17 GMT

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