BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jenim514
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

9 REPLIES 9
Reeza
Super User

PROC TRANSPOSE X 2

 

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

 

 

jenim514
Pyrite | Level 9

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;

jenim514
Pyrite | Level 9

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;

Astounding
PROC Star

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;

jenim514
Pyrite | Level 9

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;

Astounding
PROC Star

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.

jenim514
Pyrite | Level 9
Thank you so much!
AhmedAl_Attar
Rhodochrosite | Level 12

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

Ksharp
Super User
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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