Conditional Vertical Summation

[melsaid04]

I have 

ID	Date	SIN	Date30
1	12Jul2017	318	11Aug2017
1	11Aug2017	197	10Sep2017
1	10Sep2017	700	10Oct2017
1	10Oct2017	524	09Nov2017
1	09Nov2017	702	09Dec2017
2	29Sep2017	642	29Oct2017
2	09Dec2017	446	08Jan2018
3	04Dec2010	182	03Jan2011
3	02Feb2011	0	04Mar2011
3	03Apr2011	0	03May2011
4	05Dec2010	317	04Jan2011
4	06Dec2010	272	05Jan2011
4	07Dec2010	648	06Jan2011
4	08Dec2010	778	07Jan2011
4	03Mar2011	839	02Apr2011
4	27May2011	784	26Jun2011

Date30 is just the variable date + 30 days

I want to vertically sum the SIN variable By ID where the new variable SIN30 is the cumulative sum of all SIN values for the 30 days after the current date. the output should look like this 

ID	Date	SIN	Date30	SIN30
1	12Jul2017	318	11Aug2017	515
1	11Aug2017	197	10Sep2017	897
1	10Sep2017	700	10Oct2017	1224
1	10Oct2017	524	09Nov2017	1226
1	09Nov2017	702	09Dec2017	0
2	29Sep2017	642	29Oct2017	642
2	09Dec2017	446	08Jan2018	446
3	04Dec2010	182	03Jan2011	182
3	02Feb2011	0	04Mar2011	0
3	03Apr2011	0	03May2011	0
4	05Dec2010	317	04Jan2011	2015
4	06Dec2010	272	05Jan2011	1698
4	07Dec2010	648	06Jan2011	1426
4	08Dec2010	778	07Jan2011	778
4	03Mar2011	839	02Apr2011	839
4	27May2011	784	26Jun2011	784

Thanks for all your help!

[Reeza]

This is a moving sum. Do you have SAS\ETS? You can check with:

proc product_status;run;

If so, proc expand is what you want to use.

https://blogs.sas.com/content/iml/2016/01/27/moving-average-in-sas.html

If not, SQL or a data step are possible though more manual:

http://support.sas.com/kb/25/027.html

or an IML solution:

https://blogs.sas.com/content/iml/2016/02/03/rolling-statistics-sasiml.html

---

@melsaid04 wrote:

I have 

ID	Date	SIN	Date30
1	12Jul2017	318	11Aug2017
1	11Aug2017	197	10Sep2017
1	10Sep2017	700	10Oct2017
1	10Oct2017	524	09Nov2017
1	09Nov2017	702	09Dec2017
2	29Sep2017	642	29Oct2017
2	09Dec2017	446	08Jan2018
3	04Dec2010	182	03Jan2011
3	02Feb2011	0	04Mar2011
3	03Apr2011	0	03May2011
4	05Dec2010	317	04Jan2011
4	06Dec2010	272	05Jan2011
4	07Dec2010	648	06Jan2011
4	08Dec2010	778	07Jan2011
4	03Mar2011	839	02Apr2011
4	27May2011	784	26Jun2011

Date30 is just the variable date + 30 days

I want to vertically sum the SIN variable By ID where the new variable SIN30 is the cumulative sum of all SIN values for the 30 days after the current date. the output should look like this 

ID	Date	SIN	Date30	SIN30
1	12Jul2017	318	11Aug2017	515
1	11Aug2017	197	10Sep2017	897
1	10Sep2017	700	10Oct2017	1224
1	10Oct2017	524	09Nov2017	1226
1	09Nov2017	702	09Dec2017	0
2	29Sep2017	642	29Oct2017	642
2	09Dec2017	446	08Jan2018	446
3	04Dec2010	182	03Jan2011	182
3	02Feb2011	0	04Mar2011	0
3	03Apr2011	0	03May2011	0
4	05Dec2010	317	04Jan2011	2015
4	06Dec2010	272	05Jan2011	1698
4	07Dec2010	648	06Jan2011	1426
4	08Dec2010	778	07Jan2011	778
4	03Mar2011	839	02Apr2011	839
4	27May2011	784	26Jun2011	784

Thanks for all your help!

---

[novinosrin · Accepted solution]

data have;
input ID	Date :date9.	SIN	Date30 :date9. ;
format date: date9.;
cards;
1	12Jul2017	318	11Aug2017
1	11Aug2017	197	10Sep2017
1	10Sep2017	700	10Oct2017
1	10Oct2017	524	09Nov2017
1	09Nov2017	702	09Dec2017
2	29Sep2017	642	29Oct2017
2	09Dec2017	446	08Jan2018
3	04Dec2010	182	03Jan2011
3	02Feb2011	0	04Mar2011
3	03Apr2011	0	03May2011
4	05Dec2010	317	04Jan2011
4	06Dec2010	272	05Jan2011
4	07Dec2010	648	06Jan2011
4	08Dec2010	778	07Jan2011
4	03Mar2011	839	02Apr2011
4	27May2011	784	26Jun2011
;

data want;
if _N_ = 1 then do;
if 0 then set have(rename=(sin=_sin));
   declare hash h(dataset:'have(rename=(sin=_sin)');
  h.defineKey('id','date');
  h.defineData('_sin');
  h.defineDone();
end;
set have;
by id;
sin30=0;
do n=date to date30;
if h.find(key:id,key:n)=0 then sin30+_sin;
end;
drop n _sin;;
run;

If the above works, and you really understand the approach, I could further shorten it as rename and some others in the above are actually redundant, helps my eyesight

[melsaid04]

This worked perfectly! 

Thank you so much!