topic Re: make wide table long in SAS Programming

make wide table long

jenim514 — Thu, 09 Nov 2017 17:20:37 GMT

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!!

Re: make wide table long

Reeza — Thu, 09 Nov 2017 17:28:39 GMT

PROC TRANSPOSE X 2

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

Re: make wide table long

jenim514 — Thu, 09 Nov 2017 17:53:21 GMT

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;

Re: make wide table long

jenim514 — Thu, 09 Nov 2017 18:00:55 GMT

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;

Re: make wide table long

Astounding — Thu, 09 Nov 2017 18:16:49 GMT

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;

Re: make wide table long

jenim514 — Thu, 09 Nov 2017 20:30:52 GMT

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;

Re: make wide table long

Astounding — Thu, 09 Nov 2017 20:41:25 GMT

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.

Re: make wide table long

jenim514 — Thu, 09 Nov 2017 20:43:10 GMT

Thank you so much!

Re: make wide table long

AhmedAl_Attar — Thu, 09 Nov 2017 20:57:31 GMT

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

Re: make wide table long

Ksharp — Fri, 10 Nov 2017 14:16:22 GMT

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;