topic Re: Sum of values based on date range created off of the min date in SAS Programming

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

Roddy — Fri, 07 Apr 2017 13:26:40 GMT

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

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

RW9 — Fri, 07 Apr 2017 13:30:54 GMT

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;

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

Reeza — Fri, 07 Apr 2017 13:32:21 GMT

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.

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

Ksharp — Fri, 07 Apr 2017 14:14:49 GMT

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;