Transposing multiple variables from multiple time points.

[Jbhammon]

I am trying to transpose a dataset that looks like

SUBJ VISIT SITE1 SITE2 SITE3

1        1        3.5     4.5      2.5 

1        2        3.6     4.6      2.2

2        1        3.4     3.2      2.1

2        2        4.5     5.2      4.4

And I want it to look like

SUBJ  SITE1.1 SITE2.1   SITE3.1 SITE1.2 SITE2.2 SITE3.2

1         3.5        4.5           2.5         3.6        4.6         2.2

2         3.4        3.2           2.1         4.5        5.2         4.4

I want keep the existing varible names, but add .(visit) to the name to identify the visit.

I can only find ways to do one variable at a time, but need to transpose poses 30 variables with several time points.

Many thinks in advance.

John.

[art297]

I suggest using the transpose macro you can find at: http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset

after downloading and running it, you would only have to run something like:

data have;
  input SUBJ VISIT SITE1 SITE2 SITE3 test1 test2 test3;
  cards;
1        1        3.5     4.5      2.5 1 2 3
1        2        3.6     4.6      2.2 3 2 1
2        1        3.4     3.2      2.1 4 5 6
2        2        4.5     5.2      4.4 6 5 4
;
%transpose(data=have, out=want, by=subj, id=visit,
   delimiter=_, var=site1--test3)

Art, CEO, AnalystFinder.com

[ArtC]

SAS variable names cannot contain a dot (.) but we could use an underscore.  The following transposes for 5 visits using the DATA step.  This could be cleaned up and made more flexible, but it should do what you requested.

data have;
input SUBJ VISIT SITE1 SITE2 SITE3;
datalines;
1        1        3.5     4.5      2.5 
1        2        3.6     4.6      2.2
2        1        3.4     3.2      2.1
2        2        4.5     5.2      4.4
run;
data want(keep=subj site1_: site2_: site3_:);
   set have;
   by subj;
   array st1 {5} site1_1 - site1_5;
   array st2 {5} site2_1 - site2_5;
   array st3 {5} site3_1 - site3_5;
   retain site1: site2: site3: .;

   if first.subj then call missing(of st1{*} st2{*} st3{*});

   st1{visit} = site1;
   st2{visit} = site2;
   st3{visit} = site3;

   if last.subj then output want;
   run;

[Reeza]

For your arrays are you more likely to do your analysis by site or time period? Which ever one is more common should be the first prefix in your variable. SAS can use prefixes to reference a variable list, which is why you'd want to factor this into your decision making. 

[Ksharp · Accepted solution]

The simplest way is using IDGROUP. 

Or for big table, could try MERGE skill.

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

data have;
  input SUBJ VISIT SITE1 SITE2 SITE3 ;
  cards;
1        1        3.5     4.5      2.5 
1        2        3.6     4.6      2.2
2        1        3.4     3.2      2.1
2        2        4.5     5.2      4.4  
;
proc sql noprint;
select max(visit) into : max from have;
quit;


proc summary data=have ;
by subj;
output out=want idgroup(out[&max] (SITE1 SITE2 SITE3)=);
run;

[Jbhammon]

This works exactly as I was looking for.  I was used to doing this in another program but "transpose" function wasn't working for me.

Many thanks.