topic Grouping Data in New SAS User

Grouping Data

new_sas_user_4 — Tue, 27 Nov 2018 16:04:10 GMT

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:)

Re: Grouping Data

novinosrin — Tue, 27 Nov 2018 16:16:24 GMT

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;

Re: Grouping Data

new_sas_user_4 — Tue, 27 Nov 2018 16:31:17 GMT

@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?

Re: Grouping Data

novinosrin — Tue, 27 Nov 2018 16:36:51 GMT

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.

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

Re: Grouping Data

new_sas_user_4 — Tue, 27 Nov 2018 16:44:00 GMT

Thanks @novinosrin

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

Re: Grouping Data

novinosrin — Tue, 27 Nov 2018 16:46:01 GMT

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

Re: Grouping Data

new_sas_user_4 — Tue, 27 Nov 2018 16:52:58 GMT

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

Re: Grouping Data

novinosrin — Tue, 27 Nov 2018 16:57:41 GMT

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.