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;
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;
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;
Thank you learsaas.
You are very helpful. Everything works great. I owe you so much
Hi draycut
Yes I do have SAS/ETS
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.