DATA Step, Macro, Functions and more

Sum of values based on date range created off of the min date

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Sum of values based on date range created off of the min date

I am trying to sum sales for customers when the date range falls between the min date of the first sale and any sales that happened within 7 days from the min date.  

 

For example:

Customer  Home     Car     Boat     DateSold

123A         0             1         0          3/30/2017

123A         1             0         0          4/2/2017

456B         0             1         0          3/28/2017

456B         1             0         0          3/28/2017

456B         0             0         1          4/7/2017

 

I want the end result to look like:

Customer     Total

123A             2             

456B             2     **I do not want to count the Boat on 4/7 because it was sold more than 7 days from the min date for this customer

 

I have tried:

proc sql;
create table TEST as
select
Customer,
MIN(DateBound) as MinDateBound,
SUM(boat + home + car) as PoliciesSold
from
Database_Test
where
PolicyStatus_ID in (1,2) and DateBound > '27MAR2017'd
group by
Customer_ID
order by Customer
;
quit;

 

This query works great ... except for the 'only include if within 7 days filter'.

 


Accepted Solutions
Solution
‎04-11-2017 10:39 AM
Super User
Posts: 19,878

Re: Sum of values based on date range created off of the min date

If you want two passes of the data calculate the min per customer and then use 

 

where date between minDate and minDate+6

 

or you could try the following in your current query, minus your original logic for dates. 

 

having date between min(date) and min(date) + 6 

 

BETWEEN includes both ends of an interval which is why I've used 6 in above samples. 

View solution in original post


All Replies
Super User
Super User
Posts: 7,997

Re: Sum of values based on date range created off of the min date

Hi,

 

You have not posted any test data in the form of a datastep, therefore this is just a guesss:

data want;
  set have;
  retain fst_date total;
  by customer;
  if first.customer then do;
    fst_date=date_sold;
    total=0;
  end;
  if date_sold <= fst_date + 7 then total=total+sum(home,car,boot);
  if last.customer then output;
run; 
Solution
‎04-11-2017 10:39 AM
Super User
Posts: 19,878

Re: Sum of values based on date range created off of the min date

If you want two passes of the data calculate the min per customer and then use 

 

where date between minDate and minDate+6

 

or you could try the following in your current query, minus your original logic for dates. 

 

having date between min(date) and min(date) + 6 

 

BETWEEN includes both ends of an interval which is why I've used 6 in above samples. 

Super User
Posts: 10,046

Re: Sum of values based on date range created off of the min date

data have;
input Customer $ Home     Car     Boat     DateSold : mmddyy10.;
format datesold mmddyy10.;
cards;
123A         0             1         0          3/30/2017
123A         1             0         0          4/2/2017
456B         0             1         0          3/28/2017
456B         1             0         0          3/28/2017
456B         0             0         1          4/7/2017
;
run;


proc sql;
select customer,
(select count(*) from have where customer=a.customer and 
 datesold between a.datesold and a.datesold+7) as total
 from 
(
select distinct customer,datesold 
 from have
  group by customer
   having datesold=min(datesold)
) as a;
quit;
☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 117 views
  • 0 likes
  • 4 in conversation