Obtain the percent sum by multiple groups

[acrosb]

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;

[Reeza · Accepted solution]

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.

[PaigeMiller]

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.

[acrosb]

Thank you! Yes, I will present my data in a data step from now on.

[acrosb]

Thank you for your help! This is perfect.

[acrosb]

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.

[Reeza]

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;

[acrosb]

Ah, ok. Thanks again!