BookmarkSubscribeRSS Feed
newbi
SAS Employee

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

4 REPLIES 4
Hima
Obsidian | Level 7

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

Haikuo
Onyx | Level 15

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;

Tom
Super User Tom
Super User

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?

Ksharp
Super User

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

Ksharp

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 992 views
  • 0 likes
  • 5 in conversation