not able to use Lag function

[saspert]

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

[ballardw]

It would be better to provide some example data of the dataset on the Set statement in the data step that uses the Lag function, in the form of a data step is best, that recreates the issue.

Your picture is not very helpful as Lag is a dataset order dependent function and the result you show appears to be a summary or report. We would need to see values of the variables in order they appear in the data, with appropriate values of any by variables.

[Astounding]

You are using the LAG function correctly.  That's a good start.  However ... after you use LAG you then sort the data set into a different order.  Is it any wonder that the variable holding the LAG results is also in a different order?

[saspert]

@Astounding if i understood your remarks correctly, because of the different sort order after calculating the lag function, the lag values show up in the wrong order in the final report. So, the values maybe right but the report has them in the wrong order. Is that correct? 

[Astounding]

Yes, that's what I'm thinking but also note that the transpose could conceivably play a role.  Transpose can have unusual behavior when some of the transposed values are missing (transposing the nonmissings, then padding with missings).  So missing values in your data might "encourage"  you to look at transpose as well.

[Steelers_In_DC]

Depending on what you want to see you should use the lag function last, I see the proc sort has already been addressed but you also have a transpose after the lag which might get you unintended results.

[saspert]

@Steelers_In_DC I think I see your and @Astounding point. I think you are suggesting do the transpose first, get all values in order before calculating lag. 

[Steelers_In_DC]

Not necessarily the transpose, I was only suggesting that the transpose 'might' get you unintended results.  I assume you have to do the sort.  Think about what the lag function does, then think about a sort.  Judging by your picture you want to sort before using the lag function.