traspose columns into rows

[starz4ever2007]

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.

[ballardw · Accepted solution]

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;

[starz4ever2007]

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;

[novinosrin]

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;