I'm pretty far along into a report. Right now I have a data set that looks like this (not real data, i understand column 2 and 3 are not sorted, but it is in my program):
friend | treats | count |
bird | pop | 3 |
bird | eggs | 134 |
bird | fish | 12 |
cat | ham | 8 |
cat | soda | 12 |
dog | meat | 3 |
dog | eggs | 4 |
dog | fish | 1 |
dog | cereal | 1 |
dog | candy | 1 |
And I'm trying to get an output of this:
friend | treats | count | percentage |
bird | pop | 3 | 2% |
bird | eggs | 134 | 90% |
bird | fish | 12 | 8% |
cat | ham | 8 | 40% |
cat | soda | 12 | 60% |
dog | meat | 3 | 30% |
dog | eggs | 4 | 40% |
dog | fish | 1 | 10% |
dog | cereal | 1 | 10% |
dog | candy | 1 | 10% |
I'd post code that i have which attempts to do this, but suffice it to say, it's not worth sharing. Any thoughts or ideas are much appreciated. thank you.
data have;
input friend $ treats $ count;
cards;
bird pop 3
bird eggs 134
bird fish 12
cat ham 8
cat soda 12
dog meat 3
dog eggs 4
dog fish 1
dog cereal 1
dog candy 1
;
proc sql;
create table want as
select *, count/sum(count) as pct format=percent10.
from have
group by friend;
quit;
data have;
input friend $ treats $ count;
cards;
bird pop 3
bird eggs 134
bird fish 12
cat ham 8
cat soda 12
dog meat 3
dog eggs 4
dog fish 1
dog cereal 1
dog candy 1
;
proc sql;
create table want as
select *, count/sum(count) as pct format=percent10.
from have
group by friend;
quit;
This is the first I'll be using PROC SQL...thank you very much!!
@prolifious You are welcome. Yes proc SQL is super cool 🙂
And one of the other report procedures:
proc tabulate data=have; class friend treats; freq count; table friend*treats, n='Count' pctn<treats>='Percentage' *f=percent8.0 ; run;
if you do not actually need a data set
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.