Solved
Contributor
Posts: 47

# Transposing data

I am trying to transpose data that looks like

subjid  drug  week   q1  q2   q3   q4

001      A        1        4     3     3     0

001      A        8        2    2      3     0

001      A        12      2    2      3     0

001      A        16      2    2      3     0

002      B        1        2    1      3     0

002      B        8        2    3      3     0

002      B        12      1    2      3     0

002      B        16      2    2      3     0

003      A        1        2    2      3     0

003      A        8        2    2      3     0

I am trying to transpose from long to wide so that it reads something like this:

subjid  drug    wk1q1  wk1q2   wk1q3  wk1q4  wk8q1  wk8q2 wk8q3....all the way to wk16q4

001      A             4           3          3           0            2          2      3

002      B             2           1          3           0            8          2      3

003      A             1           2          2           3            0          8      2

Accepted Solutions
Solution
‎07-27-2016 10:13 PM
Posts: 5,542

## Re: Transposing data

[ Edited ]

Your data in not in a long structure. Reshape it to long, then make it extra wide:

``````data have;
input subjid  drug \$ week   q1  q2   q3   q4;
datalines;
001      A        1        4     3     3     0
001      A        8        2    2      3     0
001      A        12      2    2      3     0
001      A        16      2    2      3     0
002      B        1        2    1      3     0
002      B        8        2    3      3     0
002      B        12      1    2      3     0
002      B        16      2    2      3     0
003      A        1        2    2      3     0
003      A        8        2    2      3     0
;

proc transpose data=have out=long name=q prefix=value;
by subjid drug week;
var q:;
run;

proc transpose data=long out=extraWide(drop=_NAME_) prefix=wk;
by subjid drug;
id week q;
var value1;
run;``````

As a rule, this extra wide structure will be more cumbersome to process with SAS tools than a true long structure would.

PG

All Replies
Solution
‎07-27-2016 10:13 PM
Posts: 5,542

## Re: Transposing data

[ Edited ]

Your data in not in a long structure. Reshape it to long, then make it extra wide:

``````data have;
input subjid  drug \$ week   q1  q2   q3   q4;
datalines;
001      A        1        4     3     3     0
001      A        8        2    2      3     0
001      A        12      2    2      3     0
001      A        16      2    2      3     0
002      B        1        2    1      3     0
002      B        8        2    3      3     0
002      B        12      1    2      3     0
002      B        16      2    2      3     0
003      A        1        2    2      3     0
003      A        8        2    2      3     0
;

proc transpose data=have out=long name=q prefix=value;
by subjid drug week;
var q:;
run;

proc transpose data=long out=extraWide(drop=_NAME_) prefix=wk;
by subjid drug;
id week q;
var value1;
run;``````

As a rule, this extra wide structure will be more cumbersome to process with SAS tools than a true long structure would.

PG
Super User
Posts: 10,787

## Re: Transposing data

```If you have big table, Check the MERGE skill proposed by Me,Matt, Arthur.T :

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

data work.have;
input subjid \$ drug \$ week   q1  q2   q3   q4 ;
cards;
001      A        1        4     3     3     0
001      A        8        2    2      3     0
001      A        12      2    2      3     0
001      A        16      2    2      3     0
002      B        1        2    1      3     0
002      B        8        2    3      3     0
002      B        12      1    2      3     0
002      B        16      2    2      3     0
003      A        1        2    2      3     0
003      A        8        2    2      3     0
;
run;
data temp(index=(x=(subjid drug)));
set have;
by subjid drug;
if first.drug then n=0;
n+1;
run;
proc sql;
select distinct catt('temp(where=(n=',n,')
rename=(q1-q4=wk',week,'q1-wk',week,'q4))')
into : list separated by ' '
from temp;
quit;
data want;
merge &list;
by subjid drug;
drop n week;
run;

```
Super User
Posts: 10,787

## Re: Transposing data

```Sorry. Last code wouldn't work if there are missing levels in a group,
Try this one :

data work.have;
length STATE \$ 2 YEAR 8 RACE \$ 1 F_0-F_10 8;
input State Year Race F_0-F_10;
datalines;
CA 2010 W 10 11 12 13 14 15 16 17 18 19 20
CA 2010 B 30 31 32 33 34 35 36 37 38 39 40
CA 2010 H 50 51 52 53 54 55 56 57 58 59 60
CA 2011 W 44 44 44 44 44 44 44 44 44 44 44
CA 2011 B 22 22 22 22 22 22 22 22 22 22 22
CA 2011 H 88 88 88 88 88 88 88 88 88 88 88
;
run;

proc sql;
select distinct catt('temp(where=(state="',state,'" and year=',year,')
rename=(F_0-F_10=',race,'_F_0-',race,'_F_10))')
into : list separated by ' '
from temp;
quit;
data want;
merge &list;
by State Year;
drop n race;
run;

```
Super User
Posts: 10,787

## Re: Transposing data

```Opps.

data work.have;
input subjid \$ drug \$ week   q1  q2   q3   q4 ;
cards;
001      A        1        4     3     3     0
001      A        8        2    2      3     0
001      A        12      2    2      3     0
001      A        16      2    2      3     0
002      B        1        2    1      3     0
002      B        8        2    3      3     0
002      B        12      1    2      3     0
002      B        16      2    2      3     0
003      A        1        2    2      3     0
003      A        8        2    2      3     0
;
run;

proc sql;
select distinct catt('have(where=(week=',week,')
rename=(q1-q4=wk',week,'q1-wk',week,'q4))')
into : list separated by ' '
from have;
quit;
data want;
merge &list;
by subjid drug;
drop  week;
run;

```
☑ This topic is solved.