topic Re: Obtain the percent sum by multiple groups in SAS Programming

Obtain the percent sum by multiple groups

acrosb — Thu, 09 Feb 2023 16:38:39 GMT

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;

Re: Obtain the percent sum by multiple groups

Reeza — Thu, 09 Feb 2023 16:59:46 GMT

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.

Re: Obtain the percent sum by multiple groups

PaigeMiller — Thu, 09 Feb 2023 17:14:04 GMT

For percents, you can use PROC FREQ

proc freq data=have;
    by yyyymm;
    table channel;
    weight revenue;
run;

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.

Re: Obtain the percent sum by multiple groups

acrosb — Thu, 09 Feb 2023 17:29:15 GMT

Thank you for your help! This is perfect.

Re: Obtain the percent sum by multiple groups

acrosb — Thu, 09 Feb 2023 17:29:46 GMT

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

Re: Obtain the percent sum by multiple groups

acrosb — Thu, 09 Feb 2023 17:59:25 GMT

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.

Re: Obtain the percent sum by multiple groups

Reeza — Thu, 09 Feb 2023 18:27:49 GMT

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;

Re: Obtain the percent sum by multiple groups

acrosb — Thu, 09 Feb 2023 18:55:31 GMT

Ah, ok. Thanks again!