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
- /
- SAS Procedures
- /
- SAS Help

Topic Options

- Subscribe to 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
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-14-2013 02:26 AM

Hi All,

I have the sas dataset like:

User Rate

A 4

A 4

A 5

A 5

B 2

B 2

B 3

I would like to output :

User Rate1 Rate2

A 4 5

B 2 3

Kindly advise.

SK

Accepted Solutions

Solution

08-14-2013
03:24 AM

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

08-14-2013 03:24 AM

Proc SQL qould be good here.

data one;

input user1 $ rate;

datalines;

A 4

A 4

A 5

A 5

B 2

B 2

B 3

;

/* precautionary sort */

proc sort data=one;

by user1, rate;

run;

proc sql;

create table two as

select

max(rate) as rate1,

min(rate as rate2

from one

group by user1;

quit;

Should give desired output.

All Replies

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

08-14-2013 03:16 AM

You could solve this with data step programming using SET - BY, and using first./last.-logic and explicit OUTPUT.

Data never sleeps

Solution

08-14-2013
03:24 AM

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

08-14-2013 03:24 AM

Proc SQL qould be good here.

data one;

input user1 $ rate;

datalines;

A 4

A 4

A 5

A 5

B 2

B 2

B 3

;

/* precautionary sort */

proc sort data=one;

by user1, rate;

run;

proc sql;

create table two as

select

max(rate) as rate1,

min(rate as rate2

from one

group by user1;

quit;

Should give desired output.

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

08-14-2013 06:46 AM

Might one user have more than two rates? What then?

Should the result columns be in the order of the incoming data or ascending value? (as the original, very small, example demonstrates)

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

08-14-2013 08:32 AM

Hello Siddharth!

I have been using SAS for few months now so I am not the most experienced user. Even though there must be a more efficient way, here is what I have for you... Does this work?

/*I arbitrarily added a few more values to test*/

data one;

input user1 $ rate;

datalines;

A 4

A 4

A 5

A 5

A 2

A 1

A 6

A 3

A 5

A 4

B 2

B 2

B 3

B 5

B 6

B 8

B 6

run;

/*Sort the data by rate so that there are no duplicate values on the same letter */

proc sort data=one nodup;

by rate;

run;

/*sort the data again by User1 to be able to transpose in the next step*/

proc sort data=one;

by user1;

run;

/*Transpose and create a new data set*/

proc transpose data=one out=transposed;

by user1;

run;

/*Print to check the results*/

proc print data=transposed;

run;

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

08-14-2013 10:18 AM

The approach proposed by Greek, using Proc Transpose, is the most generic way of handling this. It will handle many conditions, such as the number of values and level of the values, that you will need to consider programmatically if using either data step or sql.

Also it is very efficient -- does its work in memory.

Larry