Extrapolating running total from .LAST total value

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Extrapolating running total from .LAST total value

Hey all,

I trying to figure out how to create a running total, if all we have is the total sum in the .LAST variable. I created a sample dataset that should make it a little clearer what I am after:

DATA SALES_DATA;

  INPUT REGION_ID STORE_ID YEAR SALES;

  DATALINES;

  1 1 2000 .

  1 1 2001 .

  1 1 2002 .

  1 1 2003 40

  1 2 1977 .

  1 2 1978 .

  1 2 1979 .

  1 2 1980 .

  1 2 1981 12

  2 3 1999 .

  2 3 2000 .

  2 3 2001 .

  2 4 2002 17

  3 4 1956 .

  3 4 1957 22

;

so, as you can see we only have data for the last time the store was in business, which includes all the sales from previous years. Assuming sales have been completely linear and added up year over year, how would I tell SAS to grab the STORE_ID.LAST value then divide it by count of years we have data for to put it into the STORE_ID.FIRST's SALES field? Once I figure out how to get the value from the last field into the first I am planning on just running the usual running total (after dividing by the count, that can be created by something like:

DATA SALES; SET SALES; BY REGION_ID STORE_ID; IF FIRST.STORE = 1 THEN COUNT =0; COUNT+1; run; 

So, ideally the final table would start of like:

DATA SALES_DATA;

  INPUT REGION_ID STORE_ID YEAR SALES;

  DATALINES;

  1 1 2000 10

  1 1 2001 20

  1 1 2002 30

  1 1 2003 40

I looked into PROC EXPAND, but I could not get it to work for my case. Any suggestions are greatly welcomed!


Accepted Solutions
Solution
‎02-03-2014 11:06 AM
Occasional Contributor
Posts: 7

Re: Extrapolating running total from .LAST total value

Chris J and RealTemper over at Stackoverflow had this alternative need solution:

proc sql;
  
create table filled(rename=(sales_filled=sales)) as
  
select REGION_ID, STORE_ID, YEAR, max(SALES)/(max(YEAR)-min(YEAR)+1)*(YEAR-min(YEAR)+1) as sales_filled
  
from sales_data
  
group by REGION_ID, STORE_ID
  
order by REGION_ID, STORE_ID, Year;
quit
;
    

View solution in original post


All Replies
Super Contributor
Posts: 644

Re: Extrapolating running total from .LAST total value

I'm not sure I completely understand what you are trying to do but a SQL step might get you closer to what you want:

Proc SQL ;

     Create table stats as

          Select     REGION_ID

                    ,     STORE_ID

               ,     count (year)     as years

               ,     max (year)      as last_year

               ,     sum (sales)      as tot_sales

               ,     sum (sales) / count (year) as avg_sales

          From     sales_data

          Group    

               by    REGION_ID

                    ,       STORE_ID

                       ;

Quit ;

[Untested]

Richard

Solution
‎02-03-2014 11:06 AM
Occasional Contributor
Posts: 7

Re: Extrapolating running total from .LAST total value

Chris J and RealTemper over at Stackoverflow had this alternative need solution:

proc sql;
  
create table filled(rename=(sales_filled=sales)) as
  
select REGION_ID, STORE_ID, YEAR, max(SALES)/(max(YEAR)-min(YEAR)+1)*(YEAR-min(YEAR)+1) as sales_filled
  
from sales_data
  
group by REGION_ID, STORE_ID
  
order by REGION_ID, STORE_ID, Year;
quit
;
    

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 184 views
  • 4 likes
  • 2 in conversation