transpose

[SAS_inquisitive]

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;

[ballardw]

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;

[SAS_inquisitive]

@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

[art297]

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

[SAS_inquisitive]

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

[jdwaterman91]

Then just use the sum function instead of the catt function

[ballardw · Accepted solution]

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;

or

proc sort data = a;

by x ;

run;

which may be important.

[art297]

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

[SAS_inquisitive]

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