SUM Based

[Mark7]

Hi All,

I am trying calculate rolling 150 days sum and then set a flag  'Y' if the sum equals to 6 or more.

In next column I am trying to  flag 'Y' when previous product was C no matter what was next A, B or C irrelevant is ‘spend’.

In third column important is to set up flag when between two dates is 4 or less days irrelevant is ‘spend’.

 Similarly for next id’s as well.

Thank you

id	date	prod	spend	rolling_spend	flag within 150 days months sum equals to 6 or more	if 25 days from previous  C	4 days  between any A, B C
1	11Dec2017	A	1	1
1	12Jan2018	B	2	3
1	23Apr2018	B	3	6	Y
1	31May2018	C	1	6	Y
1	12Jun2018	B	2	6	Y	Y
1	23Jul2018	A	3	9	Y
1	31Jul2018	C	1	10	Y
1	12Aug2018	B	2	12	Y	Y
1	23Aug2018	B	3	15	Y	Y
1	31Aug2018	C	1	16	Y
1	12Nov2018	B	2	12	Y
1	23Dec2018	C	3	12	Y
1	31Dec2018	C	1	12	Y	Y
2	01Mar2018	B	1	1
2	11Apr2018	B	1	2
2	21May2018	C	1	3
2	11Aug2018	A	2	4
2	21Aug2018	C	1	5
2	01Sep2018	C	1	6	Y	Y
3	11May2018	C	1	1
3	12May2018	B	1	2		Y	Y
3	21Jun2018	A	1	3
3	01Sep2018	A	1	4
3	11Sep2018	C	1	5
3	12Oct2018	B	1	4
3	21Nov2018	A	1	4
3	01Dec2018	C	1	5

I found a similar topic and I managed to do the first column but I have a problem with the next ones

data have ;
input id date : date10. prod $ spend ;
format date date9.;
CARDS;
1 11Dec2017 A 1
1 12Jan2018 B 2
1 23Apr2018 B 3
1 31May2018 C 1
1 12Jun2018 B 2
1 23Jul2018 A 3
1 31Jul2018 C 1
1 12Aug2018 B 2
1 23Aug2018 B 3
1 31Aug2018 C 1
1 12Nov2018 B 2
1 23Dec2018 B 3
1 31Dec2018 C 1
2 01Mar2018 B 1
2 11Apr2018 B 1
2 21May2018 C 1
2 11Aug2018 A 2
2 21Aug2018 C 1
2 01Sep2018 C 1
3 11May2018 C 1
3 12May2018 B 1
3 21Jun2018 A 1
3 01Sep2018 A 1
3 11Sep2018 C 1
3 12Oct2018 B 1
3 21Nov2018 A 1
3 01Dec2018 C 1

;;;;
proc sql;
create table want as
select
t1.id,
t1.date,
t1.spend,
sum(t2.spend) as Rolling_Spend,
case when sum(t2.spend) >=6 then 'Y' end as Flag
from
have t1
left join have t2
on t1.id=t2.id
and intck('day',t2.date,t1.date) between 0 and 150
group by t1.id, t1.date, t1.spend
order by t1.id, t1.date;
quit;

[PeterClemmensen]

Welcome to the SAS communities 🙂

Do you have SAS/ETS?

[learsaas · Accepted solution]

data want;
	set have;
	attrib rolling_spend length=8;
	attrib FLAG1 length=$1 label='flag within 150 days months sum equals to 6 or more';
	attrib FLAG2 length=$1 label='if 25 days from previous  C';
	attrib FLAG3 length=$1 label='4 days  between any A, B C';
	rolling_spend=0;FLAG1='';FLAG2='';FLAG3='';
	do i=1 to _n_;
		set have(rename=(id=id1 date=date1 prod=prod1 spend=spend1)) point=i; 
		if id=id1 and 0<=date-date1<=150 then rolling_spend+spend1;
		if id=id1 and 0<=date-date1<=25 and prod1='C' and i^=_n_ then flag2='Y';
		if id=id1 and 0<=date-date1<=4 and prod1 in ('A','B','C') and i^=_n_ then flag3='Y';
	end;
	if rolling_spend>=6 then FLAG1='Y';
	drop id1 date1 spend1 prod1 i;
run;

[Mark7]

Thank you learsaas.

You are very helpful. Everything works great. I owe you so much

[Mark7]

Hi draycut

Yes I do have SAS/ETS