proc sql noprint; create table bb_extract1 as SELECT VW_BB_TVDATA.campaign length=255 format=$255. AS campaign, VW_BB_TVDATA.Product length=255 format=$255. AS Product, VW_BB_TVDATA.YearOf length=8 format=11. AS YearOf, VW_BB_TVDATA.Month length=255 format=$255. AS Month, VW_BB_TVDATA.WeekOf length=10 format=$10. AS WeekOf, VW_BB_TVDATA.Market length=255 format=$255. AS Market, VW_BB_TVDATA.Station length=255 format=$255. AS Station, VW_BB_TVDATA.Length length=8 format=11. AS Length, VW_BB_TVDATA.DPAired length=255 format=$255. AS DPAired, VW_BB_TVDATA.DaypartAired length=255 format=$255. AS DaypartAired, VW_BB_TVDATA.TapeAired length=255 format=$255. AS TapeAired, VW_BB_TVDATA.TapeNameAired length=255 format=$255. AS TapeNameAired, VW_BB_TVDATA.ProgramAired length=255 format=$255. AS ProgramAired, VW_BB_TVDATA.DateAired length=10 format=$10. AS DateAired, VW_BB_TVDATA.TimeAired length=255 format=$255. AS TimeAired, VW_BB_TVDATA.URLAddress length=255 format=$255. AS URLAddress, VW_BB_TVDATA.SPENT length=8 format=DOLLAR23.2 AS SPENT, VW_BB_TVDATA.ClSpots length=8 format=11. AS ClSpots, VW_BB_TVDATA.GRPs length=8 AS GRPs, VW_BB_TVDATA.IMPs length=8 format=11. AS IMPs, VW_BB_TVDATA.Coupons length=8 AS Coupons, VW_BB_TVDATA.Visits length=8 AS Visits, VW_BB_TVDATA.Tests length=8 AS Tests, VW_BB_TVDATA.Store length=8 AS Store, VW_BB_TVDATA.BuyNow length=8 AS BuyNow, VW_BB_TVDATA.BuyType length=7 format=$7. AS BuyType, VW_BB_TVDATA.country length=8 format=$255. AS country, VW_BB_TVDATA.productname length=255 format=$255. AS productname, VW_BB_TVDATA.stationname length=255 format=$255. AS stationname, 'crap' as crap FROM BLBFLDB.VW_BB_TVDATA VW_BB_TVDATA; quit; proc sql; create table time_hrchy as select distinct weekof, month, yearof from BLBFLDB.VW_BB_TVDATA order by weekof ; quit; proc sql ; create table bb_extract1_smry as SELECT t1.country , t1.campaign , t1.Product , t1.Market , t1.BuyType , t1.WeekOf , Sum(t1.SPENT) as tot_SPENT, FROM bb_extract1 t1 group by t1.country, t1.campaign , t1.Product, t1.Market , t1.BuyType , t1.WeekOf order by t1.weekof ; quit; data bb_extract1_smry2 ; set bb_extract1_smry ; cpv2=tot_spent/tot_Visits; run; PROC SORT DATA=bb_extract1_smry2 OUT=bb_extract1_smry_sort nodupkey dupout=test ; BY country campaign Product Market BuyType WeekOf ; RUN; data bb_extract1_calclag (keep= country campaign product market buytype weekof tot_spent spent_pr ); set bb_extract1_smry_sort; BY country campaign Product Market BuyType WeekOf ; SPENT_pr=lag(tot_spent); if first.campaign or first.product or first.market or first.country or first.buytype then do; SPENT_pr=.; end; run; PROC SORT DATA=bb_extract1_calclag OUT=bb_extr1_smry_srt3 ; BY country campaign Product Market WeekOf ; RUN; proc transpose data=bb_extr1_smry_srt3 out=bb_extr1_smry_sort_tr (rename=(_name_=metric)) ; by country campaign Product Market WeekOf ; id buytype ; run; data bb_extr1_smry_sort_tr; set bb_extr1_smry_sort_tr; clearance=actual/planned; run; proc sort data= bb_extr1_smry_sort_tr; by country campaign Product Market metric WeekOf; quit; run; proc transpose data=bb_extr1_smry_sort_tr out=bb_extr1_smry_sort_tr2 (rename=(col1=value)); by country campaign Product Market metric WeekOf; var actual Planned clearance ; run; data bb3(drop=metric _name_); set bb_extr1_smry_sort_tr2; newmetricname= trim(left(metric))||"_"||trim(left(_name_)); if newmetricname ='tot_SPENT_Actual' then newmetricname='Actual Spend'; if newmetricname ='tot_SPENT_clearance' then newmetricname='Spend Clearance'; if newmetricname ='tot_SPENT_Planned' then newmetricname='Planned Spend'; run; TITLE; FOOTNOTE; proc sort data=bb3; by weekof; run; data bb_tr_final; merge bb3(in=a) time_hrchy(in=b); by weekof; if a then output; run; I am using the results in SAS VA 7.1. The lag function seems to work fine for a while but then gives wrong numbers soon after. See the attached pic with red arrows. thanks, saspert
... View more