Calculate Percents across table

Reply
New Contributor
Posts: 4

Calculate Percents across table

Hello,

 

Hope you are doing well.

 

PROC REPORT DATA=Mauricio_Out4 out=Rep2;
title1 "Digital% by Country - Brand";
title2 "Real Spend up to &date";
Column Country Category Brand Jan2018 Feb2018 Mar2018 Apr2018 May2018 Jun2018 Jul2018 Aug2018 Sep2018 Oct2018 Nov2018 Dec2018 ;
define Country /style(column)={cellwidth=2in} group 'Country' format=$mgrfmt.;
define Category /style(column)={cellwidth=1.50in} group format=$sctrfmt.;
define Brand /style(column)={cellwidth=1.50in} format=$sctrfmt.;
define Jan2018 / format=Percent8.2 ;
define Feb2018 / format=Percent8.2 ;
define Mar2018 / format=Percent8.2 ;
define Apr2018 / format=Percent8.2 ;
define May2018 / format=Percent8.2 ;
define Jun2018 / format=Percent8.2 ;
define Jul2018 / format=Percent8.2 ;
define Aug2018 / format=Percent8.2 ;
define Sep2018 / format=Percent8.2 ;
define Oct2018 / format=Percent8.2 ;
define Nov2018 / format=Percent8.2 ;
define Dec2018 / format=Percent8.2 ;
BREAK after Country / skip summarize dol dul;
run;
ods tagsets.excelxp close;
ods listing;

 

This is the current code i have to display the data as seen in the picture.

 

However, as you can see the percents seen after the break line are summed over 100%..

 

I need to have the break line display the percents as Jan2018.sum / Jan2018(Total)

 

And have the same show for the rest of the months.

 

If you could please help even with one month!

 

I would really appreciate it. Thanks!!

SAS Super FREQ
Posts: 9,371

Re: Calculate Percents across table

Hi, you have not provided any data for anyone to test with. Nor did you provide the custom format that you seem to use. So no one can really help you. It does not appear that you are calculating the percents with PROC REPORT. So, to me, this implies that you have some prior process that calculates the Jan2018 - Dec2018 numbers.

The default usage you have for your numeric variables is a usage of SUM. So the summary line will sum over whatever is in that column.

You could write a COMPUTE block to essentially recalculate the numbers on the break, however, I hesitate to even suggest that because without seeing your data it's hard to figure out WHERE the TOTAL for Jan 2018 is. I only see the Jan2018 number formatted as a percent. Where is the total coming from? Can you post ALL your code, including your ODS destination, your format and some test data (even if only for a few months).

BTW, not many people will open .DOCX or .XLSX files, so it is far more useful to post your data as a DATALINES in a DATA step program along with the rest of your code.

Cynthia
New Contributor
Posts: 4

Re: Calculate Percents across table

[ Edited ]
Posted in reply to Cynthia_sas

Hi Cynthia,

 

Thank you so much for the quick reply.

 

My apologies, its the first time i ever ask for help here. Let me see if i can provide the full example.

 

This the Full code i am using.

 

 

Data Mauricio.Test_File_Mau (Keep= Country Brand Category Date Media Media_type Month Media_Owner Format SellingPlatform NetMediaSpendUSD NetDigitalSpendUSD) ;
Set mon.Final_Investment;
where year(date)=2018 & NetMediaSpendUSD>0;
Format NetMediaSpendUSD Comma10.2;
Format NetDigitalSpendUSD Comma10.2;
run;
 
%_eg_conditional_dropds(WORK.QUERY_FOR_TEST_FILE_MAU1);
PROC SQL;
CREATE TABLE WORK.QUERY_FOR_TEST_FILE_MAU1 AS
SELECT t1.Country,
t1.Category,
t1.Brand,
t1.date,
t1.Media,
t1.Media_Type,
t1.Month,
t1.Media_Owner,
t1.Format,
t1.NetMediaSpendUSD,
t1.SellingPlatform,
t1.NetDigitalSpendUSD,
/* Digital_% */
((IFN(Media='DIGITAL',t1.NetDigitalSpendUSD, 0, 0))/SUM(t1.NetMediaSpendUSD)) FORMAT=Comma10.5 AS
'Digital_%'n,
/* OLV_Spend */
((IFN(Media_Type='OLV',t1.NetDigitalSpendUSD, 0, 0))) FORMAT=Comma10.5 AS OLV_Spend,
/* Programmatic_Spend */
((IFN((SellingPlatform='Programmatic' or SellingPlatform='PROGRAMMATIC'),t1.NetMediaSpendUSD, 0, 0))) FORMAT=Comma10.5 AS
Programmatic_Spend,
/* Display_Spend */
((IFN(Format='DISPLAY',t1.NetMediaSpendUSD, 0, 0)) ) FORMAT=Comma10.5 AS Display_Spend,
/* OLV%_Total_Spend */
((IFN(Media_Type='OLV',t1.NetDigitalSpendUSD, 0, 0))/SUM(t1.NetMediaSpendUSD)) FORMAT=Comma10.5 AS
'OLV%_Total_Spend'n,
/* OLV%_Total_Digital */
((IFN(Media_Type='OLV',t1.NetDigitalSpendUSD, 0, 0))/SUM(t1.NetDigitalSpendUSD)) FORMAT=Comma10.5 AS
'OLV%_Total_Digital'n,
/* Programmatic%_Total_Digital */
((IFN((SellingPlatform='Programmatic' or SellingPlatform='PROGRAMMATIC'),t1.NetMediaSpendUSD, 0, 0))/SUM(t1.NetDigitalSpendUSD))
FORMAT=Comma10.5 AS 'Programmatic%_Total_Digital'n,
/* Display%Total_Spend */
((IFN(Format='DISPLAY',t1.NetMediaSpendUSD, 0, 0)) /SUM(t1.NetMediaSpendUSD)) FORMAT=Comma10.5 AS
'Display%Total_Spend'n,
/* Display%_Total_Digital */
((IFN(Format='DISPLAY',t1.NetMediaSpendUSD, 0, 0)) /SUM(t1.NetDigitalSpendUSD)) FORMAT=Comma10.5 AS
'Display%_Total_Digital'n
 

FROM MAURICIO.TEST_FILE_MAU t1
GROUP BY t1.Country,
t1.Month,
t1.Brand,
t1.Category,
t1.Date;
QUIT;

%_eg_conditional_dropds(WORK.QUERY_FOR_TEST_FILE_MAU1_0001);
PROC SQL;
CREATE TABLE WORK.Mauricio_Output AS
SELECT t1.Country,
t1.Category,
t1.Brand,
t1.date,
t1.Media,
t1.Media_Type,
t1.Month,
t1.Media_Owner,
t1.Format,
t1.NetMediaSpendUSD,
t1.SellingPlatform,
t1.NetDigitalSpendUSD,
t1.'Digital_%'n,
t1.'OLV%_Total_Spend'n,
t1.'OLV%_Total_Digital'n,
t1.'Display%_Total_Digital'n,
t1.'Display%Total_Spend'n,
t1.'Programmatic%_Total_Digital'n

FROM WORK.QUERY_FOR_TEST_FILE_MAU1 t1 WHERE (t1.Media_Type='DIGITAL' or t1.Media_Type='OLV') ;

QUIT;

proc sql;
create table Mauricio_Output2 as
select Country, Category, Brand, Date, sum('Digital_%'n) as SpendUSD
from WORK.Mauricio_Output
group by Country, Category, Brand, Date;
quit;
proc transpose data=Mauricio_Output2 out=Mauricio_Output3 ;
by Country Category Brand;
var SpendUSD;
id Date;
run;
proc sql;
create table Mauricio_Out4 as
Select Country, Category, Brand,
'01/01/2018'n as Jan2018, '02/01/2018'n as Feb2018, '03/01/2018'n as Mar2018, '04/01/2018'n as Apr2018,
'05/01/2018'n as May2018, '06/01/2018'n as Jun2018, '07/01/2018'n as Jul2018, '08/01/2018'n as Aug2018,
'09/01/2018'n as Sep2018, '10/01/2018'n as Oct2018, '11/01/2018'n as Nov2018, '12/01/2018'n as Dec2018
from Mauricio_Output3;
quit;
ods listing close;
ods tagsets.excelxp file="C:\Users\kevespin\Desktop\MONDELEZ_MEDIAPLAN_SAS\SAS\2018\Send to mauricio\'Digital_Perc'n_KPISv1_KE.xls" style=sasweb options(Sheet_Name='Digital%_TotalSpend');
PROC REPORT DATA=Mauricio_Out4 out=Rep2;
title1 "Digital% by Country - Brand";
title2 "Real Spend up to &date";
Column Country Category Brand Jan2018 Feb2018 Mar2018 Apr2018 May2018 Jun2018 Jul2018 Aug2018 Sep2018 Oct2018 Nov2018 Dec2018 ;
define Country /style(column)={cellwidth=2in} group format=$mgrfmt.;
define Category /style(column)={cellwidth=1.50in} group format=$sctrfmt.;
define Brand /style(column)={cellwidth=1.50in} format=$sctrfmt.;
define Jan2018 / sum format=Percent8.2 ;
define Feb2018 / sum format=Percent8.2 ;
define Mar2018 / format=Percent8.2 ;
define Apr2018 / format=Percent8.2 ;
define May2018 / format=Percent8.2 ;
define Jun2018 / format=Percent8.2 ;
define Jul2018 / format=Percent8.2 ;
define Aug2018 / format=Percent8.2 ;
define Sep2018 / format=Percent8.2 ;
define Oct2018 / format=Percent8.2 ;
define Nov2018 / format=Percent8.2 ;
define Dec2018 / format=Percent8.2 ;
break after Country/suppress;
run;
ods tagsets.excelxp close;
ods listing;

 

New Contributor
Posts: 4

Re: Calculate Percents across table

Posted in reply to Cynthia_sas

Data Mauricio.Test_File_Mau (Keep= Country Brand Category Date Media Media_type Month Media_Owner Format SellingPlatform NetMediaSpendUSD NetDigitalSpendUSD) ; Set mon.Final_Investment; where year(date)=2018 & NetMediaSpendUSD>0; Format NetMediaSpendUSD Comma10.2; Format NetDigitalSpendUSD Comma10.2; run; %_eg_conditional_dropds(WORK.QUERY_FOR_TEST_FILE_MAU1); PROC SQL; CREATE TABLE WORK.QUERY_FOR_TEST_FILE_MAU1 AS SELECT t1.Country, t1.Category, t1.Brand, t1.date, t1.Media, t1.Media_Type, t1.Month, t1.Media_Owner, t1.Format, t1.NetMediaSpendUSD, t1.SellingPlatform, t1.NetDigitalSpendUSD, /* Digital_% */ ((IFN(Media='DIGITAL',t1.NetDigitalSpendUSD, 0, 0))/SUM(t1.NetMediaSpendUSD)) FORMAT=Comma10.5 AS 'Digital_%'n, /* OLV_Spend */ ((IFN(Media_Type='OLV',t1.NetDigitalSpendUSD, 0, 0))) FORMAT=Comma10.5 AS OLV_Spend, /* Programmatic_Spend */ ((IFN((SellingPlatform='Programmatic' or SellingPlatform='PROGRAMMATIC'),t1.NetMediaSpendUSD, 0, 0))) FORMAT=Comma10.5 AS Programmatic_Spend, /* Display_Spend */ ((IFN(Format='DISPLAY',t1.NetMediaSpendUSD, 0, 0)) ) FORMAT=Comma10.5 AS Display_Spend, /* OLV%_Total_Spend */ ((IFN(Media_Type='OLV',t1.NetDigitalSpendUSD, 0, 0))/SUM(t1.NetMediaSpendUSD)) FORMAT=Comma10.5 AS 'OLV%_Total_Spend'n, /* OLV%_Total_Digital */ ((IFN(Media_Type='OLV',t1.NetDigitalSpendUSD, 0, 0))/SUM(t1.NetDigitalSpendUSD)) FORMAT=Comma10.5 AS 'OLV%_Total_Digital'n, /* Programmatic%_Total_Digital */ ((IFN((SellingPlatform='Programmatic' or SellingPlatform='PROGRAMMATIC'),t1.NetMediaSpendUSD, 0, 0))/SUM(t1.NetDigitalSpendUSD)) FORMAT=Comma10.5 AS 'Programmatic%_Total_Digital'n, /* Display%Total_Spend */ ((IFN(Format='DISPLAY',t1.NetMediaSpendUSD, 0, 0)) /SUM(t1.NetMediaSpendUSD)) FORMAT=Comma10.5 AS 'Display%Total_Spend'n, /* Display%_Total_Digital */ ((IFN(Format='DISPLAY',t1.NetMediaSpendUSD, 0, 0)) /SUM(t1.NetDigitalSpendUSD)) FORMAT=Comma10.5 AS 'Display%_Total_Digital'n FROM MAURICIO.TEST_FILE_MAU t1 GROUP BY t1.Country, t1.Month, t1.Brand, t1.Category, t1.Date; QUIT; %_eg_conditional_dropds(WORK.QUERY_FOR_TEST_FILE_MAU1_0001); PROC SQL; CREATE TABLE WORK.Mauricio_Output AS SELECT t1.Country, t1.Category, t1.Brand, t1.date, t1.Media, t1.Media_Type, t1.Month, t1.Media_Owner, t1.Format, t1.NetMediaSpendUSD, t1.SellingPlatform, t1.NetDigitalSpendUSD, t1.'Digital_%'n, t1.'OLV%_Total_Spend'n, t1.'OLV%_Total_Digital'n, t1.'Display%_Total_Digital'n, t1.'Display%Total_Spend'n, t1.'Programmatic%_Total_Digital'n FROM WORK.QUERY_FOR_TEST_FILE_MAU1 t1 WHERE (t1.Media_Type='DIGITAL' or t1.Media_Type='OLV') ; QUIT; proc sql; create table Mauricio_Output2 as select Country, Category, Brand, Date, sum('Digital_%'n) as SpendUSD from WORK.Mauricio_Output group by Country, Category, Brand, Date; quit; proc transpose data=Mauricio_Output2 out=Mauricio_Output3 ; by Country Category Brand; var SpendUSD; id Date; run; proc sql; create table Mauricio_Out4 as Select Country, Category, Brand, '01/01/2018'n as Jan2018, '02/01/2018'n as Feb2018, '03/01/2018'n as Mar2018, '04/01/2018'n as Apr2018, '05/01/2018'n as May2018, '06/01/2018'n as Jun2018, '07/01/2018'n as Jul2018, '08/01/2018'n as Aug2018, '09/01/2018'n as Sep2018, '10/01/2018'n as Oct2018, '11/01/2018'n as Nov2018, '12/01/2018'n as Dec2018 from Mauricio_Output3; quit; ods listing close; ods tagsets.excelxp file="C:\Users\kevespin\Desktop\MONDELEZ_MEDIAPLAN_SAS\SAS\2018\Send to mauricio\'Digital_Perc'n_KPISv1_KE.xls" style=sasweb options(Sheet_Name='Digital%_TotalSpend'); PROC REPORT DATA=Mauricio_Out4 out=Rep2; title1 "Digital% by Country - Brand"; title2 "Real Spend up to &date"; Column Country Category Brand Jan2018 Feb2018 Mar2018 Apr2018 May2018 Jun2018 Jul2018 Aug2018 Sep2018 Oct2018 Nov2018 Dec2018 ; define Country /style(column)={cellwidth=2in} group format=$mgrfmt.; define Category /style(column)={cellwidth=1.50in} group format=$sctrfmt.; define Brand /style(column)={cellwidth=1.50in} format=$sctrfmt.; define Jan2018 / sum format=Percent8.2 ; define Feb2018 / sum format=Percent8.2 ; define Mar2018 / format=Percent8.2 ; define Apr2018 / format=Percent8.2 ; define May2018 / format=Percent8.2 ; define Jun2018 / format=Percent8.2 ; define Jul2018 / format=Percent8.2 ; define Aug2018 / format=Percent8.2 ; define Sep2018 / format=Percent8.2 ; define Oct2018 / format=Percent8.2 ; define Nov2018 / format=Percent8.2 ; define Dec2018 / format=Percent8.2 ; break after Country/suppress; run; ods tagsets.excelxp close; ods listing;
New Contributor
Posts: 4

Re: Calculate Percents across table

Hello,

 

Here is some data to hopefully help.

 

Thanks,

 

kevin

Ask a Question
Discussion stats
  • 4 replies
  • 119 views
  • 0 likes
  • 2 in conversation