transposing multiple variables

[Sandra_L]

Hi guys!

here is what i have:

and what i want:

i tried array and simle transponse with by and var but never got the right structure.

any ideas?

thanks

[Kurt_Bremser]

You'll need two transposes and a merge:

data have;
input pronum hor_nr horiz $ humus $;
cards;
203 1 Cv h4
203 2 Cv h2
205 1 Cv h0
205 2 Ah h3
205 3 Yro h0
;
run;

proc transpose data=have prefix=horiz out=want1;
by pronum;
id hor_nr;
var horiz;
run;

proc transpose data=have prefix=humus out=want2;
by pronum;
id hor_nr;
var humus;
run;

data want;
merge
  want1
  want2
;
by pronum;
run;

[Sandra_L]

hey, thanks. i could merge, but i habe 20 variables and i was looking for a smoother way without having to produce 20 new sets...

[Reeza]

http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset

This macro works well. 

[rogerjdeangelis]

HAVE                                                                                                                                                            
                                                                                                                                                                
data have;                                                                                                                                                      
input pronum hor_nr horiz $ humus $;                                                                                                                            
cards;                                                                                                                                                          
203 1 Cv h4                                                                                                                                                     
203 2 Cv h2                                                                                                                                                     
205 1 Cv h0                                                                                                                                                     
205 2 Ah h3                                                                                                                                                     
205 3 Yro h0                                                                                                                                                    
;                                                                                                                                                               
run;                                                                                                                                                            
                                                                                                                                                                
Up to 40 obs WORK.HAVE total obs=5                                                                                                                              
                                                                                                                                                                
Obs    PRONUM    HOR_NR    HORIZ    HUMUS                                                                                                                       
                                                                                                                                                                
 1       203        1       Cv       h4                                                                                                                         
 2       203        2       Cv       h2                                                                                                                         
 3       205        1       Cv       h0                                                                                                                         
 4       205        2       Ah       h3                                                                                                                         
 5       205        3       Yro      h0                                                                                                                         
                                                                                                                                                                
WANT                                                                                                                                                            
                                                                                                                                                                
                                                                                                                                                                
Up to 40 obs WORK.WANT total obs=2                                                                                                                              
                                                                                                                                                                
Obs    PRONUM    _NAME_    HORIZ1    HORIZ2    HORIZ3    HUMUS1    HUMUS2    HUMUS3                                                                             
                                                                                                                                                                
 1       203     HUMUS       Cv        Cv                  h4        h2                                                                                         
 2       205     HUMUS       Cv        Ah       Yro        h0        h3        h0                                                                               
                                                                                                                                                                
                                                                                                                                                                
COUPLE OF SOLUTIONS                                                                                                                                              
                                                                                                                                                                
SOLUTION 1 - NORMALIZE (LONG AND SKINNY)                                                                                                                        
                                                                                                                                                                
data have;                                                                                                                                                      
input pronum hor_nr horiz $ humus $;                                                                                                                            
nam='HORIZ';val=HORIZ;output;                                                                                                                                   
nam='HUMUS';val=HUMUS;output;                                                                                                                                   
drop horiz humus;                                                                                                                                               
cards;                                                                                                                                                          
203 1 Cv h4                                                                                                                                                     
203 2 Cv h2                                                                                                                                                     
205 1 Cv h0                                                                                                                                                     
205 2 Ah h3                                                                                                                                                     
205 3 Yro h0                                                                                                                                                    
;                                                                                                                                                               
run;                                                                                                                                                            
                                                                                                                                                                
                                                                                                                                                                
/*                                                                                                                                                              
Up to 40 obs WORK.HAVE total obs=10                                                                                                                             
                                                                                                                                                                
Obs    PRONUM    HOR_NR     NAM     VAL                                                                                                                         
                                                                                                                                                                
  1      203        1      HORIZ    Cv                                                                                                                          
  2      203        1      HUMUS    h4                                                                                                                          
  3      203        2      HORIZ    Cv                                                                                                                          
  4      203        2      HUMUS    h2                                                                                                                          
  5      205        1      HORIZ    Cv                                                                                                                          
  6      205        1      HUMUS    h0                                                                                                                          
  7      205        2      HORIZ    Ah                                                                                                                          
  8      205        2      HUMUS    h3                                                                                                                          
  9      205        3      HORIZ    Yro                                                                                                                         
 10      205        3      HUMUS    h0                                                                                                                          
*/                                                                                                                                                              
                                                                                                                                                                
proc transpose data=have out=havxpo;                                                                                                                            
by pronum;                                                                                                                                                      
id nam hor_nr;                                                                                                                                                  
var val;                                                                                                                                                        
;run;quit;                                                                                                                                                      
                                                                                                                                                                
                                                                                                                                                                
Up to 40 obs WORK.HAVXPO total obs=2                                                                                                                            
                                                                                                                                                                
Obs    PRONUM    _NAME_    HORIZ1    HUMUS1    HORIZ2    HUMUS2    HORIZ3    HUMUS3                                                                             
                                                                                                                                                                
 1       203      VAL        Cv        h4        Cv        h2                                                                                                   
 2       205      VAL        Cv        h0        Ah        h3       Yro        h0                                                                               
                                                                                                                                                                
USE ARRAYS   
                                                                                                                                                             
* note separated strips leading blanks;                                                                                                                         
* need to now the max dimension of (horix and humus);                                                                                                           
proc sql;select max(hor_nr) into :dim separated by'' from have;                                                                                                 
                                                                                                                                                                
                                                                                                                                                                
--------                                                                                                                                                        
       3                                                                                                                                                        
                                                                                                                                                                
                                                                                                                                                                
data havary;                                                                                                                                                    
  retain pronum horz1-horz&dim. hums1-hums&dim.;                                                                                                                
  set have;                                                                                                                                                     
  by pronum;                                                                                                                                                    
  array horizs[&dim.] $8 horz1-horz&dim.;                                                                                                                       
  array humuss[&dim.] $8 hums1-hums&dim.;                                                                                                                       
  horizs[ hor_nr]=horiz;                                                                                                                                        
  humuss[ hor_nr]=humus;                                                                                                                                        
  if last.pronum then output;                                                                                                                                   
  keep pronum hor_nr horz: hums:;                                                                                                                               
;run;quit;                                                                                                                                                      
                                                                                                                                                                
                                                                                                                                                                
Up to 40 obs WORK.HAVARY total obs=2                                                                                                                            
                                                                                                                                                                
Obs    PRONUM    HORZ1    HORZ2    HORZ3    HUMS1    HUMS2    HUMS3    HOR_NR                                                                                   
                                                                                                                                                                
 1       203      Cv       Cv                h4       h2                  2                                                                                     
 2       205      Cv       Ah       Yro      h0       h3       h0         3                                                                                     
                                                                                                                                                                
                          

[JohnHoughton · Accepted solution]

Hi

Will you go on to use  hor_nr1, hor_nr2, hor_nr3 etc variables from the final dataset? I suspect not, in which case two proc transpose and a data step can be used, and will expand to as many variables as you want (by listing them in the var statement of the first proc transpose).

data have;
input pronum hor_nr horiz $ humus $;
cards;
203 1 Cv h4
203 2 Cv h2
205 1 Cv h0
205 2 Ah h3
205 3 Yro h0
;
run;

proc transpose data=have out=dat1;
var horiz humus;
by pronum hor_nr;
run;

data dat1;
set dat1;
varnam=cats(_name_,hor_nr);
run;

proc transpose data=dat1 out=want;
by pronum;
id varnam;
var col1;
run;

(I've edited to fix an error in my first post)

[JohnHoughton]

 Hi 

I realised you can have a variable in the by statement and in the var statement of proc transpose.

So by adding the hor_nr variable in the var statement , the output looks like your original (i.e includes hor_nr1, hor_nr2 etc)

data have;
input pronum hor_nr horiz $ humus $;
cards;
203 1 Cv h4
203 2 Cv h2
205 1 Cv h0
205 2 Ah h3
205 3 Yro h0
;
run;

proc transpose data=have out=dat1;
var hor_nr horiz humus;
by pronum hor_nr;
run;

data dat1;
set dat1;
varnam=cats(_name_,hor_nr);
run;

proc transpose data=dat1 out=want;
by pronum;
id varnam;
var col1;
run;

[Sandra_L]

greaty, great! thank you verry much. this seems to me - as SAS beginner - the easiest way.

[art297]

Sandra,

Assuming that you do want the exact result you specified in your request, using the %transpose macro that Fareeza suggested with the follow command will give you a result that is identical to your example output. The only thing you would have to change is to add your other 17 variables in the var parameter. However, that can also be a varlist so it could be as simple as something like:

var hor_nr--whatever_the_last_variable_name_is

%transpose(data=have, out=want, by=pronum, id=hor_nr, var=hor_nr horiz humus)

Art

[Sandra_L]

honestly, i didn't get the idea of the macro with my basic SAS knowledge - maybe in a vew month :-). but thank's for your advice

[art297]

I agree that learning all of the language elements, in's and out's of macro programming isn't for beginners, but using an already written macro is a totally different thing. Surprise yourself! Try these two steps:

1. copy (highlight then ctrl-c) the code at http://www.sascommunity.org/mwiki/images/b/be/BB-07-2013.sas

2. paste (ctrl-v) the code in whatever window you would normally write your own code

3. run the code

4. then run the following:

data have;                                                                                                                                                      
input pronum hor_nr horiz $ humus $;                                                                                                                            
cards;                                                                                                                                                          
203 1 Cv h4                                                                                                                                                     
203 2 Cv h2                                                                                                                                                     
205 1 Cv h0                                                                                                                                                     
205 2 Ah h3                                                                                                                                                     
205 3 Yro h0
;


%transpose(data=have, out=want, by=pronum, id=hor_nr, var=hor_nr horiz humus)

[Ksharp]

It is easy for proc means + idgroup .

If you have big table try MERGE skill proposed by me ,Arthur.T , Matt :

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

data have;
input pronum hor_nr horiz $ humus $;
cards;
203 1 Cv h4
203 2 Cv h2
205 1 Cv h0
205 2 Ah h3
205 3 Yro h0
;
run;
proc sql noprint;
 select max(n) into : n
  from (select count(*) as n from have group by pronum);
quit;
proc summary data=have;
by pronum ;
output out=want(drop=_type_ _freq_) idgroup(out[&n] ( hor_nr horiz  humus)=);
run;