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

[Roddy]

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

[RW9]

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; 

[Reeza · Accepted solution]

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. 

[Ksharp]

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;