- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you learsaas.
You are very helpful. Everything works great. I owe you so much
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi draycut
Yes I do have SAS/ETS