DATA Step, Macro, Functions and more

transposing multiple variables

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

transposing multiple variables

Hi guys!

here is what i have: bsp.GIF

and what i want: bsp2.GIF

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

any ideas?

thanks


Accepted Solutions
Solution
‎06-30-2016 03:37 AM
Contributor
Posts: 39

Re: transposing multiple variables

[ Edited ]

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)

View solution in original post


All Replies
Super User
Posts: 6,936

Re: transposing multiple variables

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 17

Re: transposing multiple variables

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

Super User
Posts: 17,819

Re: transposing multiple variables

Valued Guide
Posts: 505

Re: transposing multiple variables

 

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

 

Solution
‎06-30-2016 03:37 AM
Contributor
Posts: 39

Re: transposing multiple variables

[ Edited ]

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)

Contributor
Posts: 39

Re: transposing multiple variables

[ Edited ]

 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;

Occasional Contributor
Posts: 17

Re: transposing multiple variables

greaty, great! thank you verry much. this seems to me - as SAS beginner - the easiest way.
PROC Star
Posts: 7,363

Re: transposing multiple variables

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

 

Occasional Contributor
Posts: 17

Re: transposing multiple variables

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
PROC Star
Posts: 7,363

Re: transposing multiple variables

[ Edited ]

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)

 

  

Super User
Posts: 9,681

Re: transposing multiple variables

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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 553 views
  • 5 likes
  • 7 in conversation