topic Re: Creating a counter across dates - by a categorical variable in SAS Programming

Creating a counter across dates - by a categorical variable

edwardsm — Mon, 03 Dec 2018 17:14:29 GMT

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;

Re: Creating a counter across dates - by a categorical variable

PaigeMiller — Mon, 03 Dec 2018 17:49:40 GMT

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;

Re: Creating a counter across dates - by a categorical variable

FreelanceReinh — Mon, 03 Dec 2018 18:18:05 GMT

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;

Re: Creating a counter across dates - by a categorical variable

edwardsm — Mon, 03 Dec 2018 18:26:50 GMT

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

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

Re: Creating a counter across dates - by a categorical variable

edwardsm — Mon, 03 Dec 2018 18:28:13 GMT

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

Re: Creating a counter across dates - by a categorical variable

FreelanceReinh — Mon, 03 Dec 2018 19:02:24 GMT

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;

Re: Creating a counter across dates - by a categorical variable

edwardsm — Mon, 03 Dec 2018 19:08:40 GMT

This is wonderful!

Thank-you again

Michelle