## make wide table long

Solved
Regular Contributor
Posts: 181

# make wide table long

Hi!

I have a dataset that is very wide- where the last number in the value is the visit number.

 ID Q1_1 Q2_1 Q3_1 Q1_2 Q2_2 Q3_3 A 3 3 2 0 1 2 B 1 1 0 1 2 2

Any suggestions how i can make this long, like this

 ID Visit Q1 Q2 Q3 A 1 3 3 2 A 2 0 1 2 B 1 1 1 0 B 2 1 2 2

Help is appreciated!!

Accepted Solutions
Solution
‎11-09-2017 03:42 PM
Super User
Posts: 6,642

## Re: make wide table long

[ Edited ]

If I'm picturing the data correctly (good chance of that, but not 100%), make two changes.

First, make the array for the scores a one-dimensional array:

array olds {4} Score_3-Score_6;

The array for news looks like it just gets removed.

Then add just after do visit = 1 to 4:

score = olds{visit};

Also, add SCORE to the list of variables being kept.

At least that's what I think you're trying to accomplish.  See how close that comes.

All Replies
Super User
Posts: 23,342

## Re: make wide table long

PROC TRANSPOSE X 2

One to make it long, and then one to make it 'semi wide' again.

Regular Contributor
Posts: 181

## Re: make wide table long

I tried this, and didn't work.  The V3, V4, V5, V6 are the 'visits' that I want long.  The Questions Q1-Q15 I want wide.  This output give all variable still wide with additional variables V3-V6 that have values for V3_Q1-V6_Q1 (respectively)

proc transpose data=test out=long3 prefix=V3;
by PID;
VAR V3_Q1-V3_Q15;
run;

proc transpose data=test out=long4 prefix=V4;
by PID;
VAR V4_Q1-V4_Q15;
run;

proc transpose data=test out=long5 prefix=V5;
by PID;
VAR V5_Q1-V5_Q15;
run;

proc transpose data=test out=long6 prefix=V6;
by PID;
VAR V6_Q1-V6_Q15;
run;

data long;
set test;
merge long3 (rename=(V31=V3) drop= _name_)
long4 (rename=(V41=V4) drop= _name_)
long5 (rename=(V51=V5) drop= _name_)
long6 (rename=(V61=V6) drop= _name_);
by PID;
visit=input(substr(_name_,1,2),1.);
drop _name_;
run;

Regular Contributor
Posts: 181

## Re: make wide table long

O perhaps using an array might work?  I tried something like this, but way off...

data long;
set test;
array visit3(15) v3_q1-v3_q15;
array visit4(15) v4_q1-v4_q15;
array visit5(15) v5_q1-v5_q15;
array visit6(15) v6_q1-v6_q15;
do change=1 to 15;
visit=substr(change,1,2);
output;
end;
drop v3_Q1-v3_q15 v4_Q1-v4_q15 v4_Q1-v4_q15 v4_Q1-v4_q15;
run;

Super User
Posts: 6,642

## Re: make wide table long

I would go with arrays.  There's a two-dimensional array here, but it makes the programming shorter:

data want;

set have;

array new {3} Q1 - Q3;

array old {2, 3} Q1_1 Q2_1 Q3_1 Q1_2 Q2_2 Q3_3;

do visit=1 to 2;

do k=1 to 3;

new{k} = old{visit, k};

end;

output;

end;

keep ID visit Q1-Q3;

run;

Regular Contributor
Posts: 181

## Re: make wide table long

[ Edited ]

Thank you so much. How can I add one more piece to this. I have another variable "score' over the 3 visits (just one question though)

Here is my actual code...just trying to add this score dimension.

data wide004;
set study004;
array new {15} Q1 - Q15;
array old {4, 15} V3_Q1-V3_Q15 V4_Q1-V4_Q15 V5_Q1-V5_Q15 V6_Q1-V6_Q15;
array news (4)
array olds (4,1) Score_3-Score_6;
do visit=1 to 4;
do k=1 to 15;
new{k} = old{visit, k};
end;
output;
end;
keep PID visit Q1-Q15;
run;

Solution
‎11-09-2017 03:42 PM
Super User
Posts: 6,642

## Re: make wide table long

[ Edited ]

If I'm picturing the data correctly (good chance of that, but not 100%), make two changes.

First, make the array for the scores a one-dimensional array:

array olds {4} Score_3-Score_6;

The array for news looks like it just gets removed.

Then add just after do visit = 1 to 4:

score = olds{visit};

Also, add SCORE to the list of variables being kept.

At least that's what I think you're trying to accomplish.  See how close that comes.

Regular Contributor
Posts: 181

## Re: make wide table long

Thank you so much!
Super Contributor
Posts: 278

## Re: make wide table long

If you don't to restrict yourself to specific array dimensions, then you may try this approach

``````DATA have;
LENGTH ID \$1 Q1_1 Q2_1 Q3_1 Q1_2 Q2_2 Q3_2 4;
INPUT ID \$1 Q1_1 Q2_1 Q3_1 Q1_2 Q2_2 Q3_2;
DATALINES;
A 3 3 2 0 1 2
B 1 1 0 1 2 2
;
RUN;

/* Reverse Engineer the Columns Composition */
DATA WORK.SRTD(KEEP=ID VISIT QTR COUNT);
SET WORK.HAVE;
ARRAY cols {*} _NUMERIC_;
LENGTH ID \$1 VISIT 4 QTR \$2 COUNT 4;

do i=1 to dim(cols);
COUNT=cols(i);
name = VNAME(cols(i));
VISIT= INPUT(SCAN(name,2,'_'),best.);
QTR= SCAN(name,1,'_');
OUTPUT;
end;
RUN;

PROC SORT DATA=WORK.SRTD;
BY ID VISIT;
RUN;

/* Produce Desired Output Layout */
PROC TRANSPOSE DATA=WORK.SRTD OUT=WORK.WANT(DROP=_NAME_);
ID QTR;
BY ID VISIT;
VAR COUNT;
RUN;

/* Clean Up */
PROC DATASETS LIB=WORK NOLIST;
DELETE SRTD;
RUN; QUIT;``````

Hope this helps,

Ahmed

Super User
Posts: 10,695

## Re: make wide table long

``````DATA have;
LENGTH ID \$1 Q1_1 Q2_1 Q3_1 Q1_2 Q2_2 Q3_2 4;
INPUT ID \$1 Q1_1 Q2_1 Q3_1 Q1_2 Q2_2 Q3_2;
DATALINES;
A 3 3 2 0 1 2
B 1 1 0 1 2 2
;
RUN;

proc transpose data=have out=temp;
by id;
var Q:;
run;
data temp;
set temp;
group=scan(_name_,-1,'_');
name=scan(_name_,1,'_');
run;
proc transpose data=temp out=want;
by id group;
id name;
var col1;
run;``````
☑ This topic is solved.