SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

traspose columns into rows

Accepted Solution Solved
Reply
Contributor
Posts: 62
Accepted Solution

traspose columns into rows

 

I have two datasets.

One looks like this:

 

subjid    include     wk      q1 q2 q3 qtotal

   1           1              1       3   4   6     13

   1           1              8       3   4   6     13

   1            .            12       3   4   6     13

   1           1             16       3   4   6     13

   2           1              1       3   4   6     13

 

and another data set that looks like this:

 

subjid   include      v2_a    v2_b    v2_c   v3_a    v3_b    v3_c   v4_a    v4_b    v4_c   v5_a    v5_b    v5_c  

1                 1           8          6           .          4        5            6        7           8         2         3           6        5  

2                 1           1         7           .          4        5            .         5           8         2         3          4        5          

 

I basically want a merge of the two where the dataset to look like this:

 

subjid    include     wk      q1 q2 q3 qtotal   a   b  c

   1           1              1       3   4   6     13     8   6   .

   1           1              8       3   4   6     13     4   5   6

   1            .            12       3   4   6     13     7   8   2

   1           1             16       3   4   6     13    3    6   5

   2           1              1       3   4   6     13     1    7   7

 

basically the v2 v3 v4 v5 of the second dataset correspond to the week numbers of 1 8 12 16 from the first dataset

 

I know I have to use proc transpose on the second data set to get started before merging the two datasets. But I can't seem to figure out how to get it so that v2-v5 are weeks 1-16.

 

 


Accepted Solutions
Solution
2 weeks ago
Super User
Posts: 13,941

Re: traspose columns into rows

Posted in reply to starz4ever2007

Something like this might get close:

You did not provide any actual data sets so substitute your names where appropriate. This assumes both of your starting sets have been sorted by subjid week and that the only values for week that appear are 1,8,12 and 16.

 

data temp;
   set anotherset;
   array aa v2_a v3_a v4_a v4a;
   array bb v2_b v3_b v4_b v4b;
   array cc v2_c v3_c v4_c v4c;
   array wk{4} _temporary_(1,8,12,16);
   do i= 1 to dim(aa);
      a= aa[i];
      b= bb[i];
      c= cc[i]; 
      week = wk[i];
      output;
   end;
   keep sujid include a b c;
run;

data want;
   merge firstset temp;
   by subjid week;
run;

View solution in original post


All Replies
Solution
2 weeks ago
Super User
Posts: 13,941

Re: traspose columns into rows

Posted in reply to starz4ever2007

Something like this might get close:

You did not provide any actual data sets so substitute your names where appropriate. This assumes both of your starting sets have been sorted by subjid week and that the only values for week that appear are 1,8,12 and 16.

 

data temp;
   set anotherset;
   array aa v2_a v3_a v4_a v4a;
   array bb v2_b v3_b v4_b v4b;
   array cc v2_c v3_c v4_c v4c;
   array wk{4} _temporary_(1,8,12,16);
   do i= 1 to dim(aa);
      a= aa[i];
      b= bb[i];
      c= cc[i]; 
      week = wk[i];
      output;
   end;
   keep sujid include a b c;
run;

data want;
   merge firstset temp;
   by subjid week;
run;
Contributor
Posts: 62

Re: traspose columns into rows

just realized this now....

 

should there be a v5? so that it reads:

 

array aa v2_a v3_a v4_a v5_a v5a;

 

instead of:

 

array aa v2_a v3_a v4_a v4a;

Super User
Posts: 2,061

Re: traspose columns into rows

[ Edited ]
Posted in reply to starz4ever2007
data one;
input subjid    include     wk      q1 q2 q3 qtotal;
cards;
   1           1              1       3   4   6     13
   1           1              8       3   4   6     13
   1            .            12       3   4   6     13
   1           1             16       3   4   6     13
   2           1              1       3   4   6     13
   ;
 
data two;
input subjid   include      v2_a    v2_b    v2_c   v3_a    v3_b    v3_c   v4_a    v4_b    v4_c   v5_a    v5_b    v5_c  ;
cards;
1                 1           8          6           .          4        5            6        7           8         2         3           6        5  
2                 1           1         7           7          4        5            .         5           8         2         3          4        5  
;
data want;
do n=1 by 1 until(last.subjid);
merge one two;
by subjid;
array t(4,3) v2_a--v5_c;
array k(*) a b c;
do j=1 to 3;
k(j)=t(n,j);
end;
output;
end;
drop v2_a--v5_c n j;
run;
☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 154 views
  • 3 likes
  • 3 in conversation