DATA Step, Macro, Functions and more

Lag Function - Last week amount

Reply
SAS Employee
Posts: 73

Lag Function - Last week amount

I have table-A with following columns:

YearWeekNoSalesRepIDRegionAmount
201210011$100
201210021$200
201210031$300
201220011$400
201220031$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;

YearWeekNoSalesRepIDRegionAmountLastWeekAmount
201210011$100$0
201210021$200$0
201210031$300$0
201220011$400$100
201220031$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:

YearWeekNoRegionAmountLastWeekAmount
201211$600$0
201222$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

Regular Contributor
Posts: 233

Lag Function - Last week amount

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

Respected Advisor
Posts: 3,156

Lag Function - Last week amount

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;

Super User
Super User
Posts: 7,039

Lag Function - Last week amount

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?

Super User
Posts: 10,023

Lag Function - Last week amount

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

Ksharp

Ask a Question
Discussion stats
  • 4 replies
  • 366 views
  • 0 likes
  • 5 in conversation