Grouping Data

[new_sas_user_4]

Hi Everyone,

I have 3 columns in my table - category, name and count 

The data is arranged by Category and count(desc)

I want the data to be grouped based on the category and the top 2 counts AND the rest (for that category) grouped together as Othr with the total of the counts(for that category) ..

eg: Below is the data I have :

data have;
input Cat $5. name $5. count ;
datalines;
Tech john 100
Tech Harr 90
Tech Rick 60
Tech Ronn 50
Food Gini 20
Food kell 15
Food Dina 10
Cars Rini 50
Cars Holl 40
Cars Radd 30
Cars lola 20
Cars Lili 50
;

The output I want is :

Tech john 100
Tech Harr 90

Tech Othr 110     /* which is 60+50 for the remaining Tech*/

Food Gini 20
Food kell 15

Food Othr 10

Cars Rini 50
Cars Holl 40

Cars Othr 100

How can this be done?

Thanks for the help in advance:)

[novinosrin · Accepted solution]

data have;
input Cat $ name $ count ;
datalines;
Tech john 100
Tech Harr 90
Tech Rick 60
Tech Ronn 50
Food Gini 20
Food kell 15
Food Dina 10
Cars Rini 50
Cars Holl 40
Cars Radd 30
Cars lola 20
Cars Lili 50
;

data want;
do _n_=1 by 1 until(last.cat);
set have;
by cat notsorted;
if _n_>2 then temp=sum(count,temp);
if last.cat then do;
name='Other';
count=temp;
end;
if _n_<=2 or last.cat then output;
end;
drop temp;
run;

[new_sas_user_4]

@novinosrin thanks a lot!!

Could you please suggest a few topics that I should study or practice to be able to write such queries by myself?

[novinosrin]

Basically, I rely on books by Author Ron Cody Learning SAS by example . Top notch book in my opinion especially the 2nd edition

Then SAS functions by the same author Ron cody

For macros, I think author is Art carpenter

But first try to get hold of the top one i.e Author Ron Cody Learning SAS by example . Read page by page, line by line by taking your time all that you need, Trust me, you will start helping others users with your super codes. 

PS

I myself can't believe for real how amazing that book is, It's unreal and classic

[new_sas_user_4]

Thanks @novinosrin

and the book  Author Ron Cody Learning SAS by example  is good for a basic SAS user like me?(to start) 😛

[novinosrin]

That book helped a spoiled only child IKEA fork lift driver, pubaholic who played nintendo, sega, play station, xbox to where I am today. That must mean something? lol

[new_sas_user_4]

I look up to you for SAS!! Thanks a lot 🙂 I am going to get that book!!

[novinosrin]

Indeed 🙂 the way to go. I think @ShelleySessoms may help you letting you know if there are discounts by SAS press. I vaguely remember there was a huge discount a few months ago but lasted only a couple of days though. But I have a paid safarionline subscription anyway so it didn't matter to me.