Hi,
I have data that looks like this:
yyyymm channel revenue
202001 standard $150
202001 non-standard $200
202001 hybrid $50
202002 standard $100
202002 non-standard $100
202002 hybrid $100
I want to report the percent of revenue coming from each channel in each month. Here is the output I'm looking for:
202001 202002
standard 37.5% 33.3%
non-standard 50% 33.3%
hybrid 12.5% 33.3%
The below sql code give me the correct numbers (the percent), but the format of the table/output is not want I want. I attempted to do the procedure in proc freq and proc tabulate, but I can't seem to get those to work. The freq code below takes a really long time to run, so I actually don't know if it does give the correct results. Can anyone help with this?
proc sql; create table temp2 as select yyyymm, channel, revenue, revenue/sum(revenue) as pctPop format=percent8.1 from (select yyyymm, channel, sum(revenue) as revenue from temp1 group by yyyymm, channel) group by yyyymm; select * from temp2; quit; proc freq data=temp1; tables revenue*(yyyymm channel); run; proc tabulate data=temp1; class channel yyyymm; var revenue; table channel, pctsum*niw*yyyymm; run;
data have;
informat yyyymm yymmn6. channel $15. revenue dollar.;
format yyyymm yymmn6. revenue dollar8.;
input yyyymm channel revenue ;
cards;
202001 standard $150
202001 non-standard $200
202001 hybrid $50
202002 standard $100
202002 non-standard $100
202002 hybrid $100
;;;;
run;
proc tabulate data=have;
class channel yyyymm;
table channel, yyyymm*(colpctn);
freq revenue;
run;
This should get you closer using just PROC TABULATE if your dollars are all whole units. Not sure how it works if they're decimals.
data have;
informat yyyymm yymmn6. channel $15. revenue dollar.;
format yyyymm yymmn6. revenue dollar8.;
input yyyymm channel revenue ;
cards;
202001 standard $150
202001 non-standard $200
202001 hybrid $50
202002 standard $100
202002 non-standard $100
202002 hybrid $100
;;;;
run;
proc tabulate data=have;
class channel yyyymm;
table channel, yyyymm*(colpctn);
freq revenue;
run;
This should get you closer using just PROC TABULATE if your dollars are all whole units. Not sure how it works if they're decimals.
For percents, you can use PROC FREQ
proc freq data=have;
by yyyymm;
table channel;
weight revenue;
run;
or
proc freq data=have;
table channel*yyyymm/norow nopercent;
weight revenue;
run;
@acrosb Please notice how @Reeza has modified your data to be SAS data step code. That is how you should present data from now on.
Thank you! Yes, I will present my data in a data step from now on.
Thank you for your help! This is perfect.
When I output the Tabulate results (using "out=datasetname" in the proc tabulate statement), the columns/rows flip. Is there any (simple) way to keep the format of the table when putting it into a dataset? I was the years/months to be columns in my outputted dataset.
Unfortunately not, but you can use PROC TRANSPOSE to flip it back.
proc transpose data=long out=want;
by channel;
id yyyymm;
var pctn_01;
run;
Ah, ok. Thanks again!
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 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: