Creating a counter across dates - by a categorical variable

[edwardsm]

Good day all!

Looking for some help with a twist on a counter variable question.  I have the following data - temperatures taken on each day of the year for a number of years.  I need to count the number of days the temperature is below 5, between 5-10, between 10-15, etc... My goal is to have a dataset that shows me what temperature range (defined by a new variable called code) the observation falls in and how many days the temperature was within that given range.  So we might have a run where the temperature was below 5 for 10 days then between 5 and 10 for 2 days, and then back below 5 for another 3 days, etc....  An example of the final dataset might be something like this:

Date Temp Code #days
01-Mar-15 -13.33  1 1
02-Mar-15 -12.77   1 2
03-Mar-15 -14.44   1 3
04-Mar-15 -22.22   1 4
05-Mar-15 -15.55   1 5
06-Mar-15 -7.22   1 6
07-Mar-15 -8.33   1 7
08-Mar-15 -4.44   1 8
09-Mar-15 -1.66   1 9
10-Mar-15 3.33   1 10
11-Mar-15 0   1  11
12-Mar-15 4.44   1 12
13-Mar-15 3.33   1 13
14-Mar-15 5.55   2 1
15-Mar-15 9.44   2 2
16-Mar-15 -1.11   1 1
17-Mar-15 -1.11   1 2

So, on March 8, the temperature was below 5 for 8 days, and on March 15, the temperature was between 5 and 10 for 2 days.

I thought I could start by creating a new variable called "code" that would identify which temperature range each observation would fall into.  Then I tried using it to create my counter and well... that's when everything failed.  Most examples I've found use the sort, by, first. ...  and that doesn't work with my data, since I want to count the number of times a code appears in order of the dates.  I hope this makes sense and that someone can help me out.

Here's a snippet of the data and code so far.

Thank-you!
Michelle

Data temp;

  input weather_date date10.  temp;

  datalines;

01-Mar-15 -13.33
02-Mar-15 -12.77
03-Mar-15 -14.44
04-Mar-15 -22.22
05-Mar-15 -15.55
06-Mar-15 -7.22
07-Mar-15 -8.33
08-Mar-15 -4.44
09-Mar-15 -1.66
10-Mar-15 3.33
11-Mar-15 0
12-Mar-15 4.44
13-Mar-15 3.33
14-Mar-15 5.55
15-Mar-15 9.44
16-Mar-15 -1.11
17-Mar-15 -1.11
18-Mar-15 0
19-Mar-15 3.33
20-Mar-15 -5.55

;

  format weather_data date10.;

Run;

Data test2;
  set weather;
  if temp le 5 then code = 1;
  if temp > 5 and temp le 10 then code = 2;
  if temp > 10 and temp le 15 then code = 3;
  if temp > 15 and temp le 20 then code = 4;
  if temp > 20 then code = 5;
Run;

[PaigeMiller]

Create a new variable where the value of temperature is rounded to the nearest 5 degrees. Then do some counting

Data temp;

  input weather_date date10.  temp;
  temp_rounded=round(temp,5);
  prev_temp_rounded=lag(temp_rounded);
  if prev_temp_rounded^=temp_rounded then counter=0;
  else counter+1;
  datalines;
...
run;

[edwardsm]

Thank-you for helping out.  When I tried your proposed code it was close but the counter wasn't quite doing what I needed it to do.

Michelle

[FreelanceReinh · Accepted solution]

Hi Michelle and welcome to the SAS Support Communities!

Here's another suggestion:

data want;
set temp;
code=min(max(ceil(temp/5), 1), 5);
if dif(code) then days=1;
else days+1;
run;

[edwardsm]

Thank-you!!!  This worked like a charm!

Have a wonderful day and thank- you for the help!
Michelle

[FreelanceReinh]

You're welcome!

Just in case you'll need more flexibility for the definition of CODE at some point (e.g. with varying interval lengths so that a simple formula like ceil(temp/5) would no longer work), you can also create and use a format. The example below uses your current definition.

proc format;
value tempf
low -  5  = '1'
  5<- 10  = '2'
 10<- 15  = '3'
 15<- 20  = '4'
 20<-high = '5';
run;

data want(drop=_t);
set temp(rename=(temp=_t));
by _t groupformat notsorted;
format _t tempf.;
temp=_t;
code=input(put(_t, tempf.), 8.);
if first._t then days=1;
else days+1;
run;

[edwardsm]

This is wonderful!


Thank-you again

Michelle