DATA Step, Macro, Functions and more

make wide table long

Accepted Solution Solved
Reply
Regular Contributor
Posts: 181
Accepted Solution

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.

View solution in original post


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 ]
Posted in reply to Astounding

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

Posted in reply to Astounding
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.

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

Discussion stats
  • 9 replies
  • 362 views
  • 6 likes
  • 5 in conversation