topic Re: How to use to pivot my data using proc tabulate or proc transpose in New SAS User

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

roll4life — Tue, 05 Nov 2019 13:20:22 GMT

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,

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

PeterClemmensen — Tue, 05 Nov 2019 13:26:16 GMT

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

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

PaigeMiller — Tue, 05 Nov 2019 13:36:07 GMT

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.

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

PeterClemmensen — Tue, 05 Nov 2019 13:41:34 GMT

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;

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

DWilson — Tue, 05 Nov 2019 17:48:47 GMT

@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;

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

mkeintz — Tue, 05 Nov 2019 18:27:43 GMT

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;