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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.