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:)
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;
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;
@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?
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
Thanks @novinosrin
and the book Author Ron Cody Learning SAS by example is good for a basic SAS user like me?(to start) 😛
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
I look up to you for SAS!! Thanks a lot 🙂 I am going to get that book!!
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.
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.