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'.
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.
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;
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.