topic Re: transpose in SAS Programming

transpose

SAS_inquisitive — Thu, 29 Jun 2017 19:23:39 GMT

Hello,

How to get the desired output without using TRANSPOSE (entirely in data step)?

data a;
	input x $ y $ z;
	cards;
t1 a 1
t2 a 1
t3 a 1
t1 b 2
t2 b 2
t3 b 2
;
run;

proc sort data = a;
	by x;
run;

proc transpose data = a out=b (drop = _name_);
	by x;
	var y;
run;

data want;
	set b;
	y=cats(col1,col2);
	drop col1 col2;
run;

Re: transpose

ballardw — Thu, 29 Jun 2017 19:46:38 GMT

Is there any reason you included your variable Z in data set A since it has no bearing or presence in the end result?

For your limited example this replicates the result.

ata a;
	input x $ y $ z;
	cards;
t1 a 1
t2 a 1
t3 a 1
t1 b 2
t2 b 2
t3 b 2
;
run;

proc sort data = a;
	by x y;
run;

data want;
   set a;
   by x;
   length result $ 100;
   retain result;
   if first.x then result=y;
   else result= cats(result,y);
   if last.x;
   keep x result;
run;

Re: transpose

art297 — Thu, 29 Jun 2017 19:50:57 GMT

Or, if you want an approach that uses the lag function:

data want;
  set a (drop=z);
  by x;
  y=ifc(last.x,catt(lag(y),y),'');
  if last.x then output;
run;

Art, CEO, AnalystFinder.com

Re: transpose

SAS_inquisitive — Thu, 29 Jun 2017 20:40:52 GMT

@ballardw Thank you. I also meant to add the values of z for a and b of y for each x creating third column.

x result z

t1 ab 3

t2 ab 3

t3 ab 3

Re: transpose

art297 — Thu, 29 Jun 2017 20:48:16 GMT

Easy enough to add the 3rd column:

data want (drop=_:);
  set a (rename=(z=_z));
  by x;
  y=ifc(last.x,catt(lag(y),y),'');
  z=ifc(last.x,catt(lag(_z),_z),'');
  if last.x then output;
run;

Art, CEO, AnalystFinder.com

Re: transpose

SAS_inquisitive — Thu, 29 Jun 2017 20:52:28 GMT

@art297 I meant to add (1+2) = 3, not concatenate.

Re: transpose

jdwaterman91 — Thu, 29 Jun 2017 21:00:20 GMT

Then just use the sum function instead of the catt function

Re: transpose

ballardw — Thu, 29 Jun 2017 21:05:07 GMT

Here's an extended version of my previous to encorporate a sum.

data want;
   set a;
   by x;
   length result $ 100;
   retain result zsum;
   if first.x then do;
      result=y;
      zsum=z;
   end;
   else do;
      result= cats(result,y);
      zsum = sum(zsum,z);
   end;
   if last.x;
   keep x result zsum;
run;

As a suggestion for future questions, it would be a better example if the row results are not essentially the same as your result may not demonstrate the actualy desired result well.

data a;
	input x $ y $ z;
	cards;
t1 a 1
t2 a 2
t3 c 4
t1 b 5
t2 c 6
t3 b 7
;
run;

for instance will get different results when using

proc sort data = a;

by x y;

run;

proc sort data = a;

by x ;

run;

which may be important.

Re: transpose

SAS_inquisitive — Thu, 29 Jun 2017 21:11:51 GMT

@art297 Thank you. This answer also serves my purpose, but can accept only one soution.