topic Re: Proc Transpose output help in SAS Programming

Proc Transpose output help

adityaa9z — Sat, 18 Feb 2017 15:14:21 GMT

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

Re: Proc Transpose output help

art297 — Sat, 18 Feb 2017 15:31:58 GMT

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

Re: Proc Transpose output help

adityaa9z — Sat, 18 Feb 2017 15:49:13 GMT

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?

Re: Proc Transpose output help

art297 — Sat, 18 Feb 2017 15:57:50 GMT

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

Re: Proc Transpose output help

ilikesas — Sun, 19 Feb 2017 16:42:33 GMT

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!

Re: Proc Transpose output help

art297 — Sun, 19 Feb 2017 16:50:26 GMT

@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