BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
roll4life
Fluorite | Level 6

 

Hi, My data is currently organized as:

 

NAMECOLORPROFILEHEIGHT
K2000REDC 5.10 
K2001WHITEB 7.11 
K2001BLACKB 5.12 
K2001BLUEB 5.2 
K2002BLUEA 9.3 
K2002REDA 8.2 
K2006WHITED 5.5 
K2007WHITEA 8.6 
K2007BLUEA 5.7 
K2009WHITED 8.8 
K2010BLACKB 5.9 
K2011REDB 9.6 
K2012REDC 7.7 
K2012BLUEC 9.6 
K2012WHITEC 7.5 
K2012BLACKC 8.9 

 

I would like it to look like:

 

NAMEPROFILEREDWHITEBLACKBLUE
K2000C 5.10    
K2001B  7.11  5.12  5.2 
K2002A 8.2    9.3 
K2006D  5.5   
K2007A  8.6   5.7 
K2009D  8.8   
K2010B   5.9  
K2011B 9.6    
K2012C 7.7  7.5  8.9  9.6 

 

Regards,

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

5 REPLIES 5
PeterClemmensen
Tourmaline | Level 20

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

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
PeterClemmensen
Tourmaline | Level 20

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
Pyrite | Level 9

@roll4life wrote:

 

Hi, My data is currently organized as:

 

NAMECOLORPROFILEHEIGHT
K2000REDC 5.10 
K2001WHITEB 7.11 
K2001BLACKB 5.12 
K2001BLUEB 5.2 
K2002BLUEA 9.3 
K2002REDA 8.2 
K2006WHITED 5.5 
K2007WHITEA 8.6 
K2007BLUEA 5.7 
K2009WHITED 8.8 
K2010BLACKB 5.9 
K2011REDB 9.6 
K2012REDC 7.7 
K2012BLUEC 9.6 
K2012WHITEC 7.5 
K2012BLACKC 8.9 

 

I would like it to look like:

 

NAMEPROFILEREDWHITEBLACKBLUE
K2000C 5.10    
K2001B  7.11  5.12  5.2 
K2002A 8.2    9.3 
K2006D  5.5   
K2007A  8.6   5.7 
K2009D  8.8   
K2010B   5.9  
K2011B 9.6    
K2012C 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
PROC Star

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;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 5 replies
  • 1042 views
  • 4 likes
  • 5 in conversation