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;
