topic Conditional Vertical Summation in SAS Programming

Conditional Vertical Summation

melsaid04 — Sun, 15 Jul 2018 17:30:05 GMT

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!

Re: Conditional Vertical Summation

Reeza — Sun, 15 Jul 2018 18:55:42 GMT

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!

Re: Conditional Vertical Summation

novinosrin — Sun, 15 Jul 2018 20:20:29 GMT

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

Re: Conditional Vertical Summation

melsaid04 — Sun, 15 Jul 2018 21:42:34 GMT

This worked perfectly!

Thank you so much!