Creating multiple sums of a variable with overlapping time periods

Reply
Frequent Learner
Posts: 1

Creating multiple sums of a variable with overlapping time periods

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!

Super User
Posts: 21,530

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

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!


 

Contributor
Posts: 29

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

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.

 

Respected Advisor
Posts: 2,170

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

[ Edited ]

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=p1upn25lbfo6... which seems to meet your needs as stated in your original post.

--
Paige Miller
Super User
Posts: 12,148

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

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;
Ask a Question
Discussion stats
  • 4 replies
  • 117 views
  • 3 likes
  • 5 in conversation