topic Re: Creating binary variables for unique IDs in SAS Enterprise Guide

Creating binary variables for unique IDs

TemenuzhkaPanayotova — Thu, 02 Jul 2015 08:49:49 GMT

Hello! I have the following question.

I am using SAS Enterprise Guide and I have the data structured like that:

Client ID: Product:

1 A

1 B

2 A

2 D

2 C

3 B

What I am trying to do is create binary variables for every product for every customer ID so that I can built a predictive model in SAS Enterprise Miner.

I want the data to look like that:

Client ID: Product A: Product B: Product C: Product 😧

1 1 1 0 0

2 1 0 1 1

3 0 1 0 0

The problem is that there are over 50 different products, and each client has different number of products and the ordinary functions are not working. Is there any other way to create that table with a query?

Thank

Re: Creating binary variables for unique IDs

Ksharp — Thu, 02 Jul 2015 13:29:58 GMT

Code: Program

data have;
input ClientID   Product $;
cards;
1 A
1 B
2 A
2 D
2 C
3 B
;
run;
data have;
 set have;
 retain v 1;
run;
proc transpose data=have out=temp(drop=_:) prefix=Product_;
by ClientID;
var v;
id Product;
run;
proc stdize data=temp out=want missing=0 reponly;run;

Re: Creating binary variables for unique IDs

TemenuzhkaPanayotova — Thu, 02 Jul 2015 20:12:13 GMT

Thank you! Is there a way how to do it without listing the variables and the data, because I have 50 000 rows of observations?

Re: Creating binary variables for unique IDs

PGStats — Thu, 02 Jul 2015 22:16:42 GMT

The first step in Xia's code is only there to create some example data. Use your own dataset instead. You could start with the second step as :

data have;

set myLibrary.myDataset;

retain v 1;

run;