CODEWITHSUNDEEP
sas
Demetri Pananos
Demetri Pananos

Reputation: 7404

How can I manipulate this data set to give a new column?

I have a dataset that looks like

ID  retailer    
1   A            
2   A               
1   B              
3   C                
3   B

What I would like is a data set with a column for each distinct retailer indicating if the ID is associated with the retailer (something like a boolean would do fine).

I could do this manually with proc sql but that doesn't generalize well when I have a lot of retailers.

Is there anyway to create a pivot table with SAS? I know proc tabulate can do something similar, but I need the output for further calculations.

Desired output:

ID  A  B  C      
1   1  1  0
2   1  0  0
3   0  1  1

Upvotes: 0

Views: 51

Answers (1)

Allan Bowe
Allan Bowe

Reputation: 12691

SAS can't really do pivot tables, but you can certainly swap your rows for columns using the transpose procecure - as follows:

data have;
input ID $ retailer $  ;
retain count 1; /* NOTE - this bit is necessary to have in your source */
datalines;
1   A            
2   A               
1   B              
3   C                
3   B 
;
run;

/* sort to enable transpose */
proc sort; by id;run;

/* transpose data */
proc transpose data=have out=want (drop=_name_);
  by id;
  id retailer;
run;

/* turn missings into zeros */
proc stdize data=want reponly missing=0;
run;

example result

Upvotes: 1

Related Questions