DATA Step, Macro, Functions and more

transpose

Accepted Solution Solved
Reply
Super Contributor
Posts: 271
Accepted Solution

transpose

[ Edited ]

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;

 


Accepted Solutions
Solution
‎06-29-2017 05:09 PM
Super User
Posts: 11,343

Re: transpose

Posted in reply to SAS_inquisitive

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.

View solution in original post


All Replies
Super User
Posts: 11,343

Re: transpose

Posted in reply to SAS_inquisitive

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;
Super Contributor
Posts: 271

Re: transpose

[ Edited ]

@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

PROC Star
Posts: 7,491

Re: transpose

Posted in reply to SAS_inquisitive

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

 

Super Contributor
Posts: 271

Re: transpose

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

Contributor
Posts: 22

Re: transpose

Posted in reply to SAS_inquisitive

Then just use the sum function instead of the catt function

Solution
‎06-29-2017 05:09 PM
Super User
Posts: 11,343

Re: transpose

Posted in reply to SAS_inquisitive

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.

PROC Star
Posts: 7,491

Re: transpose

Posted in reply to SAS_inquisitive

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

Super Contributor
Posts: 271

Re: transpose

[ Edited ]

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

☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 287 views
  • 4 likes
  • 4 in conversation