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!!
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.
PROC TRANSPOSE X 2
One to make it long, and then one to make it 'semi wide' again.
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;
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;
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;
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;
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.
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
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.