topic Re: transposing multiple variables in SAS Programming

transposing multiple variables

Sandra_L — Wed, 29 Jun 2016 13:25:10 GMT

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

Re: transposing multiple variables

Kurt_Bremser — Wed, 29 Jun 2016 13:44:15 GMT

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;

Re: transposing multiple variables

Reeza — Wed, 29 Jun 2016 13:45:38 GMT

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

This macro works well.

Re: transposing multiple variables

Sandra_L — Wed, 29 Jun 2016 13:49:33 GMT

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...

Re: transposing multiple variables

JohnHoughton — Wed, 29 Jun 2016 15:39:29 GMT

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)

Re: transposing multiple variables

rogerjdeangelis — Wed, 29 Jun 2016 15:23:34 GMT

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

Re: transposing multiple variables

JohnHoughton — Wed, 29 Jun 2016 15:53:35 GMT

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;

Re: transposing multiple variables

art297 — Wed, 29 Jun 2016 17:06:39 GMT

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

Re: transposing multiple variables

Ksharp — Thu, 30 Jun 2016 05:40:38 GMT

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;

Re: transposing multiple variables

Sandra_L — Thu, 30 Jun 2016 07:40:26 GMT

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

Re: transposing multiple variables

Sandra_L — Thu, 30 Jun 2016 07:42:23 GMT

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

Re: transposing multiple variables

art297 — Thu, 30 Jun 2016 15:46:59 GMT

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)