BookmarkSubscribeRSS Feed
KEVIN814
Calcite | Level 5

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!!

4 REPLIES 4
Cynthia_sas
SAS Super FREQ
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
KEVIN814
Calcite | Level 5

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;

 

KEVIN814
Calcite | Level 5

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;
KEVIN814
Calcite | Level 5

Hello,

 

Here is some data to hopefully help.

 

Thanks,

 

kevin

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 447 views
  • 0 likes
  • 2 in conversation