DATA Step, Macro, Functions and more

Proc Transpose output help

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

Proc Transpose output help

Below is the DS "wide". 

 

data wide;
input subj$ x1 x2 x3 x4 x5 y1 y2 y3 y4 y5;
cards;
001 8 5 6 5 4 10 20 30 40 50
002 7 5 6 4 5 11 33 29 34 56
003 2 2 4 5 6 22 38 21 20 34
;
run;

 

I need output using proc transpose. First 10 obs of LONG DB should look like this.

 

Subj Time X Y

001   1      8 10

001   2      5 20

001   3      6 30

001   4      5 40  

001   5      4 50

002   1      7 11

002   2      5 33

002   3      6 29

002   4      4 34

002   5      5 56

 

 


Accepted Solutions
Solution
‎02-18-2017 10:53 AM
PROC Star
Posts: 7,487

Re: Proc Transpose output help

Posted in reply to adityaa9z

Why do you NEED to use proc transpose? The problem is solved more easily with a data step:

 

data want (keep=subj time x y);
  set wide;
  array _x x1-x5;
  array _y y1-y5;
  do time=1 to 5;
    x=_x(time);
    y=_y(time);
    output;
  end;
run;

Art, CEO, AnalystFinder.com

 

View solution in original post


All Replies
Solution
‎02-18-2017 10:53 AM
PROC Star
Posts: 7,487

Re: Proc Transpose output help

Posted in reply to adityaa9z

Why do you NEED to use proc transpose? The problem is solved more easily with a data step:

 

data want (keep=subj time x y);
  set wide;
  array _x x1-x5;
  array _y y1-y5;
  do time=1 to 5;
    x=_x(time);
    y=_y(time);
    output;
  end;
run;

Art, CEO, AnalystFinder.com

 

Contributor
Posts: 27

Re: Proc Transpose output help

Thanks Art. This works.

Can you please help me understand this. Still a learner.

x=_x(time);
y=_y(time);

What does _x and _y do?
PROC Star
Posts: 7,487

Re: Proc Transpose output help

Posted in reply to adityaa9z

If you look at the code:

data want (keep=subj time x y);
  set wide;
  array _x x1-x5;
  array _y y1-y5;
  do time=1 to 5;
    x=_x(time);
    y=_y(time);
    output;
  end;
run;

_x is the name assigned to an array that will contain the five values of x1 thru x5

_y is the name assigned to an array that will contain the five values of y1 thru y5

 

a do loop is then used to create the variable TIME and, within the loop, the values of x and y are assigned the values contained in the 1st thru fifth elements of the two arrays.

 

BTW, here is how you could do the same thing using proc transpose (but using more code):

 

proc transpose data=wide out=needx (rename=(_name_=_time col1=x));
  var x1--x5;
  by subj;
run;

proc transpose data=wide out=needy (rename=(_name_=_time col1=y));
  var y1--y5;
  by subj;
run;

data want (drop=_:);
  set needx;
  set needy;
  time=input(compress(_time,'y'),8.);
run;

data want;
  retain subj time x y;
  set want2;
run;

 

HTH,

Art, CEO, AnalystFinder.com

 

Super Contributor
Posts: 441

Re: Proc Transpose output help

Hi art297,

 

I would like to ask you a small question about the code please.

 

I see that the variable "subj" isn't included in the loop, but nonetheless each subj is repeated 5 times - so that means that when you set the data "wide", the subsequent loop is applied to each observation of the original data "wide", as if there is a loop within a loop?

 

Thanks!

PROC Star
Posts: 7,487

Re: Proc Transpose output help

@ilikesas: subj, x1, x2, x3, x4, x5, y1, y2, y3, y4, and y5 were all populated by the set statement and would have been present in the output file, but I excluded them by including a keep option in the file declared by the data statement.

The do loop was accomplished within each iteration. Thus, yes, it is like a loop within a loop because the normal datastep is, in fact, a loop where _n_ defines which iteration (record) the code is working on.

 

Art, CEO, AnalystFinder.com

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 214 views
  • 3 likes
  • 3 in conversation