turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- transpose

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-29-2017 03:21 PM - edited 06-29-2017 03:23 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to SAS_inquisitive

06-29-2017 05:05 PM

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.

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to SAS_inquisitive

06-29-2017 03:46 PM

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;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

06-29-2017 04:25 PM - edited 06-29-2017 04:40 PM

@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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to SAS_inquisitive

06-29-2017 04:48 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to art297

06-29-2017 04:52 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to SAS_inquisitive

06-29-2017 05:00 PM

Then just use the sum function instead of the catt function

Solution

06-29-2017
05:09 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to SAS_inquisitive

06-29-2017 05:05 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to SAS_inquisitive

06-29-2017 03:50 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to art297

06-29-2017 05:10 PM - edited 06-29-2017 05:11 PM

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