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;
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.
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;
@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
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
@art297 I meant to add (1+2) = 3, not concatenate.
Then just use the sum function instead of the catt function
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.
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
@art297 Thank you. This answer also serves my purpose, but can accept only one soution.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.