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,
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;
Do you want a data set like this or is it for reporting purposes?
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.
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;
@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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.