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

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

1 ACCEPTED SOLUTION

Accepted Solutions
JohnHoughton
Quartz | Level 8

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

11 REPLIES 11
Kurt_Bremser
Super User

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
Calcite | Level 5

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

rogerjdeangelis
Barite | Level 11

 

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
Quartz | Level 8

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
Quartz | Level 8

 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
Calcite | Level 5
greaty, great! thank you verry much. this seems to me - as SAS beginner - the easiest way.
art297
Opal | Level 21

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
Calcite | Level 5
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
Opal | Level 21

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
Super User

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;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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