topic Re: Transposing multiple variables from multiple time points. in SAS Programming

Transposing multiple variables from multiple time points.

Jbhammon — Sat, 06 May 2017 23:16:11 GMT

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.

Re: Transposing multiple variables from multiple time points.

art297 — Sat, 06 May 2017 23:48:23 GMT

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

Re: Transposing multiple variables from multiple time points.

ArtC — Sat, 06 May 2017 23:57:28 GMT

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;

Re: Transposing multiple variables from multiple time points.

Reeza — Sun, 07 May 2017 00:30:49 GMT

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.

Re: Transposing multiple variables from multiple time points.

Ksharp — Sun, 07 May 2017 02:58:57 GMT

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;

Re: Transposing multiple variables from multiple time points.

Jbhammon — Sun, 07 May 2017 17:58:52 GMT

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.