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

Solved
Occasional Contributor
Posts: 5

# 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: 24,025

## 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.

All Replies
Super User
Posts: 9,855

## 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: 24,025

## 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,850

## 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.