topic Lag Function - Last week amount in SAS Programming

Lag Function - Last week amount

newbi — Wed, 21 Mar 2012 19:11:57 GMT

I have table-A with following columns:

Year	WeekNo	SalesRepID	Region	Amount
2012	1	001	1	$100
2012	1	002	1	$200
2012	1	003	1	$300
2012	2	001	1	$400
2012	2	003	1	$500

Then I created new dataset to add "Last Week Amount"

Data Work.test;

Set work.tableA;

by Year, WeekNo, SalesRepID, Region;

LastWeekAmount = lag(Amount);

run;

Year	WeekNo	SalesRepID	Region	Amount	LastWeekAmount
2012	1	001	1	$100	$0
2012	1	002	1	$200	$0
2012	1	003	1	$300	$0
2012	2	001	1	$400	$100
2012	2	003	1	$500	$300

Next, when i query the previous table for Year, WeekNo, Region, Amount and LastWeekAmount - the value of "LastWeekAmount" does not show the correct value.

Select Year, WeekNo, Region, sum(Amount), sum(LastWeekAmount)

From work.test

Group by Year, WeekNo, Region

Results:

Year	WeekNo	Region	Amount	LastWeekAmount
2012	1	1	$600	$0
2012	2	2	$900	$400 (this value shold be $600)

Last week amount for weekNo2 shows $400 because i'm missing the SalesRepID "003" for weekno2.

How can i sum the last week amount to show the value of $600 for weekno.2 ?

Thanks

Lag Function - Last week amount

Hima — Wed, 21 Mar 2012 19:26:58 GMT

data temp;
input Year WeekNo SalesRepID Region Amount;
cards;
2012 1 001 1 100
2012 1 002 1 200
2012 1 003 1 300
2012 2 001 1 400
2012 2 003 1 500
;run;

proc print; run;

data temp1;
set temp;
LastWeekAmount = lag(Amount);
LastWeekAmount1 = lag(LastWeekAmount);
run;
proc print; run;

proc sql;
select sum(LastWeekAmount1) from temp1;
quit;

Output for temp1:

                                                       Last       Last
                            Week    Sales                         Week       Week
             Obs    Year     No     RepID    Region    Amount    Amount    Amount1

              1     2012      1       1         1        100         .         .
              2     2012      1       2         1        200       100         .
              3     2012      1       3         1        300       200       100
              4     2012      2       1         1        400       300       200
              5     2012      2       3         1        500       400       300

Sum:

ƒƒƒƒƒƒƒƒ
600

Lag Function - Last week amount

Haikuo — Wed, 21 Mar 2012 19:35:41 GMT

There you go:

data have;

input year WeekNo SalesRepID :$3. Region Amount dollar8.;

cards;

2012 1 001 1 $100

2012 1 002 1 $200

2012 1 003 1 $300

2012 2 001 1 $400

2012 2 003 1 $500

;

data want (keep=year WeekNo Region LastWeekAmount total rename=total=amount);

LastWeekAmount=total;

total=0;

do until (last.weekno);

set have;

by year weekno;

total+amount;

end;

output;

run;

proc print;run;

Lag Function - Last week amount

Tom — Wed, 21 Mar 2012 19:51:10 GMT

Your example table showing LastWeekAmount does not match your program. Your program is just using the value from the previous observation whether it is for another week or salesrep.

Why not just summarize by week and THEN apply the LAG() function?

Lag Function - Last week amount

Ksharp — Thu, 22 Mar 2012 08:43:24 GMT

Not quit understand what you mean. But Hash Table looks good.

Ksharp