topic Re: Creating multiple sums of a variable with overlapping time periods in SAS Programming

Creating multiple sums of a variable with overlapping time periods

CalebN — Wed, 31 Jan 2018 17:35:36 GMT

Hello, I'm new to sas using the university edition and having trouble getting multiple sums of a variable. I have rainfall data from 1975 to the present with each day a numeric day of the year (365 or 366 if it's a leap year). I want to make sums of many overlapping time periods. See my code below for what I've tried.

data rainfallsummary;
set work.import;
*this determines leap year;
if (mod(year,4)=0 and(mod(year,100) ne 0 or(mod(year,100)=0 and (mod(year,400)=0)))) then leap=1; else leap=0;
*may;
if ((leap=0) and ((day>=121)and (day<=151)) )then month=5;
if ((leap=1) and ((day>=122)and (day<=152)) )then month=5;
*june;
if ((leap=0) and ((day>=152)and (day<=181)) )then month=6;
if ((leap=1) and ((day>=153)and (day<=182)) )then month=6;
*july;
if ((leap=0) and ((day>=182)and (day<=212)) )then month=7;
if ((leap=1) and ((day>=183)and (day<=213)) )then month=7;
*august;
if ((leap=0) and ((day>=213)and (day<=243)) )then month=8;
if ((leap=1) and ((day>=214)and (day<=244)) )then month=8;
*june 15 to july 15;
*if ((leap=0) and ((day>=166)and (day<=196)) )then month=6.5;
*if ((leap=1) and ((day>=167)and (day<=197)) )then month=6.5;
run;

proc means data=rainfallsummary;
class year month;
var rain;
output out=work.rainTotals sum=sum;
run;

I want to be able to get the sum of the rain during days 166 to 196 but this doesn't work with my code since I have more than one "month" for days 166 to 196.

Is there any way to solve this?

Thanks!

Re: Creating multiple sums of a variable with overlapping time periods

Reeza — Wed, 31 Jan 2018 17:39:25 GMT

What are the rules for the overlapping periods? This sounds like a moving sum?

If so, using an approach as outlined here is likely a better method:

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

You're also likely better off using a format to create your date ranges, than many if/then statements.

@CalebN wrote:

Hello, I'm new to sas using the university edition and having trouble getting multiple sums of a variable. I have rainfall data from 1975 to the present with each day a numeric day of the year (365 or 366 if it's a leap year). I want to make sums of many overlapping time periods. See my code below for what I've tried.

data rainfallsummary;
set work.import;
*this determines leap year;
if (mod(year,4)=0 and(mod(year,100) ne 0 or(mod(year,100)=0 and (mod(year,400)=0)))) then leap=1; else leap=0;
*may;
if ((leap=0) and ((day>=121)and (day<=151)) )then month=5;
if ((leap=1) and ((day>=122)and (day<=152)) )then month=5;
*june;
if ((leap=0) and ((day>=152)and (day<=181)) )then month=6;
if ((leap=1) and ((day>=153)and (day<=182)) )then month=6;
*july;
if ((leap=0) and ((day>=182)and (day<=212)) )then month=7;
if ((leap=1) and ((day>=183)and (day<=213)) )then month=7;
*august;
if ((leap=0) and ((day>=213)and (day<=243)) )then month=8;
if ((leap=1) and ((day>=214)and (day<=244)) )then month=8;
*june 15 to july 15;
*if ((leap=0) and ((day>=166)and (day<=196)) )then month=6.5;
*if ((leap=1) and ((day>=167)and (day<=197)) )then month=6.5;
run;

proc means data=rainfallsummary;
class year month;
var rain;
output out=work.rainTotals sum=sum;
run;

I want to be able to get the sum of the rain during days 166 to 196 but this doesn't work with my code since I have more than one "month" for days 166 to 196.

Is there any way to solve this?

Thanks!

Re: Creating multiple sums of a variable with overlapping time periods

MINX — Wed, 31 Jan 2018 20:11:09 GMT

If you just want to get the sum of the rain during days 166 to 196 per month per year, add where statement in Proce means procedure as:

where 166<=day<=196;

Otherwise, please provide an example output.

Re: Creating multiple sums of a variable with overlapping time periods

PaigeMiller — Wed, 31 Jan 2018 20:15:11 GMT

Sums of many overlapping groupings can be done using a Multilabel format, and then running the data through PROC MEANS or PROC SUMMARY. An example of multilabel formats are shown here: http://documentation.sas.com/?cdcId=pgmmvacdc&cdcVersion=9.4&docsetId=proc&docsetTarget=p1upn25lbfo6mkn1wncu4dyh9q91.htm&locale=en#n0h1r41mckp14dn1a02y5utzrcet which seems to meet your needs as stated in your original post.

Re: Creating multiple sums of a variable with overlapping time periods

ballardw — Wed, 31 Jan 2018 21:02:14 GMT

I would suggest using day and year to create a SAS date value and then manipulate from there with date functions and or formats.

You do not mention anything about your range of years but a custom multilabel format could be made to make the correct group:

data work.example;
   input year day rain;
   date= datejul(year*1000+day);
   format date date9.;
   Month=month(date);
datalines;
2016 1   1
2016 59  2
2016 60  3
2016 61  4
2016 123  5
2016 155  .4
2016 168  .3
2017 1    .4
2017 59   .5
2017 60   .6
2017 61   .7
2017 123  .8
2017 155  .4
2017 167  .3
;
run;


proc format library=work;
value custmonth (multilabel notsorted)
'01Jan2016'd - '31DEC2016'd = [year8.]
'01Jan2016'd - '31DEC2016'd = [mmyy8.]
'16JUN2016'd - '30Jun2016'd = '6.5M2016'
'01Jan2017'd - '31DEC2017'd = [year8.]
'01Jan2017'd - '31DEC2017'd = [mmyy8.]
'16JUN2017'd - '30Jun2017'd = '6.5M2017'
;
run;

proc means data=work.example;
   class date /mlf;
   format date custmonth. ;
   var rain;
   output out=work.exampletotals sum=sum;
run;
proc sort data=work.exampletotals;
by date;
run;