SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
msf2021
Fluorite | Level 6

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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;

View solution in original post

4 REPLIES 4
PeterClemmensen
Tourmaline | Level 20

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

msf2021
Fluorite | Level 6

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

PeterClemmensen
Tourmaline | Level 20

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
Super User Tom
Super User

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

sas-innovate-white.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9.

 

Save $200 when you sign up by March 14!

Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 481 views
  • 1 like
  • 3 in conversation