BookmarkSubscribeRSS Feed
7 REPLIES 7
dcruik
Lapis Lazuli | Level 10

Below is a way to do it using a macro and the Transpose Procedure and then merging the transposed data sets together.  It should get you what you are looking for in the output data.  If arrays are required, that can be adjusted too.

 

data have;
input ID$ Time X Y Z;
datalines;
1 10 1 2 3
1 11 4 5 6
1 12 7 8 9
2 13 10 20 30
2 14 . . .
2 15 40 . 50
3 16 15 . .
3 17 25 36 37
3 18 35 36 37
;
run;

proc sort data=have;
by ID;
run;

%macro transpose(var,n);
%if &n^=3 %then %do;
proc transpose data=have prefix=&var out=want_&var (drop=_NAME_);
by ID;
var &var;
run;
%end;
%else %do;
proc transpose data=have prefix=&var out=want_&var (drop=_NAME_);
by ID;
var &var;
run;

data want;
merge want_X want_Y want_&var;
run;
%end;
%mend;
%transpose(X,1)
%transpose(Y,2)
%transpose(Z,3);
Ngkl2001
Calcite | Level 5
thank you for your help and good answer. however, marco, proc transpose are not allowed. i can only use array data step to do it. sorry .
Ngkl2001
Calcite | Level 5
here is my raw data file, in my google drive
https://drive.google.com/open?id=0BzqllIWlzJosWGZSdmkzZ0RIeDA
FreelanceReinh
Jade | Level 19

I think you can find the solution in this article (see Example 1 there).

Ngkl2001
Calcite | Level 5
thank you very much. let me study it first. really appreciate your help
Ngkl2001
Calcite | Level 5
libname rawfile 'c:\';
run;
proc sort data=rawfile.man;
by id;
run;

data ROWOBS;
set rawfile.man;
by id;
array x_var(3) X1-X3 ;
array y_var(3) Y1-Y3 ;
array z_var(3) Z1-Z3;
if first.id then do i = 1 to 3;
x_var(i) = .;
y_var(i) = .;
z_var(i) = .;
end;
j = time;
x_var(j) = x;
y_var(j) = y;
z_var(j) = z;
if last.id then output;
retain x1-x3 y1-y3 z1-z3;
drop i j time x y z;
run;
Reeza
Super User
Here's a good walk through for future reference.

http://www.ats.ucla.edu/stat/sas/modules/widetolong_data.htm

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
  • 7 replies
  • 950 views
  • 0 likes
  • 4 in conversation