DATA Step, Macro, Functions and more

Transposing multiple variables from multiple time points.

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Transposing multiple variables from multiple time points.

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.


Accepted Solutions
Solution
‎05-07-2017 01:59 PM
Super User
Posts: 9,691

Re: Transposing multiple variables from multiple time points.

[ Edited ]

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;

 

View solution in original post


All Replies
PROC Star
Posts: 7,366

Re: Transposing multiple variables from multiple time points.

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

Valued Guide
Posts: 632

Re: Transposing multiple variables from multiple time points.

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;
Super User
Posts: 17,958

Re: Transposing multiple variables from multiple time points.

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. 

Solution
‎05-07-2017 01:59 PM
Super User
Posts: 9,691

Re: Transposing multiple variables from multiple time points.

[ Edited ]

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;

 

Occasional Contributor
Posts: 7

Re: Transposing multiple variables from multiple time points.

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.

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 235 views
  • 2 likes
  • 5 in conversation