How to use to pivot my data using proc tabulate or proc transpose

[roll4life]

Hi, My data is currently organized as:

NAME	COLOR	PROFILE	HEIGHT
K2000	RED	C	5.10
K2001	WHITE	B	7.11
K2001	BLACK	B	5.12
K2001	BLUE	B	5.2
K2002	BLUE	A	9.3
K2002	RED	A	8.2
K2006	WHITE	D	5.5
K2007	WHITE	A	8.6
K2007	BLUE	A	5.7
K2009	WHITE	D	8.8
K2010	BLACK	B	5.9
K2011	RED	B	9.6
K2012	RED	C	7.7
K2012	BLUE	C	9.6
K2012	WHITE	C	7.5
K2012	BLACK	C	8.9

I would like it to look like:

NAME	PROFILE	RED	WHITE	BLACK	BLUE
K2000	C	5.10
K2001	B		7.11	5.12	5.2
K2002	A	8.2			9.3
K2006	D		5.5
K2007	A		8.6		5.7
K2009	D		8.8
K2010	B			5.9
K2011	B	9.6
K2012	C	7.7	7.5	8.9	9.6

Regards,

[PeterClemmensen]

Do you want a data set like this or is it for reporting purposes?

[PaigeMiller]

This is very simple to do in PROC REPORT. 

proc report data=have;
    columns name profile color,height;
    define name/group;
    define profile/group;
    define color/across;
    define height/sum;
run;

This assumes you only have one record for each name/color/profile combination, which is how your data appears to be set up. If there's more than one record for each name/color/profile combination then this would need to be modified.

PROC REPORT can also produce an output data set, but if all you want is the output data set and not a report of some sort, I would probably choose PROC TRANSPOSE.

[PeterClemmensen]

Here is a proc tabulate way

data have;
input NAME $ COLOR $ PROFILE $ HEIGHT;
datalines;
K2000 RED C 5.10 
K2001 WHITE B 7.11 
K2001 BLACK B 5.12 
K2001 BLUE B 5.2 
K2002 BLUE A 9.3 
K2002 RED A 8.2 
K2006 WHITE D 5.5 
K2007 WHITE A 8.6 
K2007 BLUE A 5.7 
K2009 WHITE D 8.8 
K2010 BLACK B 5.9 
K2011 RED B 9.6 
K2012 RED C 7.7 
K2012 BLUE C 9.6 
K2012 WHITE C 7.5 
K2012 BLACK C 8.9 
;

proc tabulate data=have;
    class name profile color;
    var height;
    table name*profile,
          height=''*color=''*sum=''*f=best.;
run;

[DWilson]

---

@roll4life wrote:

Hi, My data is currently organized as:

NAME	COLOR	PROFILE	HEIGHT
K2000	RED	C	5.10
K2001	WHITE	B	7.11
K2001	BLACK	B	5.12
K2001	BLUE	B	5.2
K2002	BLUE	A	9.3
K2002	RED	A	8.2
K2006	WHITE	D	5.5
K2007	WHITE	A	8.6
K2007	BLUE	A	5.7
K2009	WHITE	D	8.8
K2010	BLACK	B	5.9
K2011	RED	B	9.6
K2012	RED	C	7.7
K2012	BLUE	C	9.6
K2012	WHITE	C	7.5
K2012	BLACK	C	8.9

I would like it to look like:

NAME	PROFILE	RED	WHITE	BLACK	BLUE
K2000	C	5.10
K2001	B		7.11	5.12	5.2
K2002	A	8.2			9.3
K2006	D		5.5
K2007	A		8.6		5.7
K2009	D		8.8
K2010	B			5.9
K2011	B	9.6
K2012	C	7.7	7.5	8.9	9.6

Regards,

---

If you want a data set:

proc sort data=mydata;

by name profile color; 

run;

proc transpose data=mydata out=newdata(drop=_name_);

by name profile;

var height;

id color;

run;

Notes: The above code doesn't guarantee that the red, white, black, and blue columns will be in the order you have shown. You can reorder them on printing/output.

Height values are treated as numeric so missing values show up as periods instead of blanks as you have shown.

run;

[mkeintz · Accepted solution]

Although I like the tabulae and transpose solutions, I think in this case a merge is a bit more intuitive:

data have;
input NAME $ COLOR $ PROFILE $ HEIGHT;
datalines;
K2000 RED C 5.10 
K2001 WHITE B 7.11 
K2001 BLACK B 5.12 
K2001 BLUE B 5.2 
K2002 BLUE A 9.3 
K2002 RED A 8.2 
K2006 WHITE D 5.5 
K2007 WHITE A 8.6 
K2007 BLUE A 5.7 
K2009 WHITE D 8.8 
K2010 BLACK B 5.9 
K2011 RED B 9.6 
K2012 RED C 7.7 
K2012 BLUE C 9.6 
K2012 WHITE C 7.5 
K2012 BLACK C 8.9 
;
data want;
  merge have (where=(color='RED') rename=(height=red))
        have (where=(color='WHITE') rename=(height=white))
        have (where=(color='BLACK') rename=(height=black))
        have (where=(color='BLUE') rename=(height=blue))
        ;
  by name profile;
  drop color;
run;