topic Re: retain and count in New SAS User

retain and count

linlin87 — Thu, 03 Apr 2025 17:04:03 GMT

Dear SAS New User,

Please can help with this problem, it trying to detect and enumerate drops in relative humidity (RH) by geographical location. The drop needs enumerating for later analysis.

Defining drop in RH follows rules:
1) to be a drop in RH it must have 3 consecutive datapoint in humidity below a threshold (less than or equal 30%).
2) The drop in humidity finishes stop if there are 3 consecutive points above threshold (31% and above).
3) Then on the next started drop (within that location), it labels drop as +1.

See data I given. Very grateful for help! I try retain and count but I never manage.

datetime	loc	RH (%)	RH drop number
21NOV20:00:59	a	33	.
21NOV20:01:04	a	31	.
21NOV20:01:09	a	30	.
21NOV20:01:14	a	30	.
21NOV20:01:19	a	29	1
21NOV20:01:24	a	28	1
21NOV20:01:29	a	27	1
21NOV20:01:34	a	27	1
21NOV20:01:39	a	27	1
21NOV20:01:44	a	26	1
21NOV20:01:49	a	28	1
21NOV20:01:54	a	27	1
21NOV20:01:59	a	26	1
21NOV20:02:04	a	25	1
21NOV20:02:09	a	24	1
21NOV20:02:14	a	23	1
21NOV20:02:19	a	21	1
21NOV20:02:24	a	22	1
21NOV20:02:29	a	18	1
21NOV20:02:34	a	20	1
21NOV20:02:39	a	27	1
21NOV20:02:44	a	29	1
21NOV20:02:49	a	32	.
21NOV20:02:54	a	31	.
21NOV20:02:59	a	32	.
21NOV20:03:04	a	29	.
21NOV20:03:09	a	29	.
21NOV20:03:14	a	29	2
21NOV20:03:19	a	28	2
21NOV20:03:24	a	28	2
21NOV20:03:29	a	29	2
21NOV20:03:34	a	28	2

Re: retain and count

PaigeMiller — Thu, 03 Apr 2025 15:51:46 GMT

Please provide data as working SAS data step code (examples and instructions)

How can row 5 show (RH Drop Number) = 1 since there have not been "3 consecutive datapoint in humidity below a threshold (30%)" ???

Re: retain and count

data_null__ — Thu, 03 Apr 2025 15:56:39 GMT

@PaigeMiller wrote:

Please provide data as working SAS data step code (examples and instructions)

How can row 5 show (RH Drop Number) = 1 since there have not been "3 consecutive datapoint in humidity below a threshold (30%)" ???

data rh;
   input datetime :datetime.  loc $  RH RHDrop;
   cards;
21NOV20:00:59  a  33 .
21NOV20:01:04  a  31 .
21NOV20:01:09  a  30 .
21NOV20:01:14  a  30 .
21NOV20:01:19  a  29 1
21NOV20:01:24  a  28 1
21NOV20:01:29  a  27 1
21NOV20:01:34  a  27 1
21NOV20:01:39  a  27 1
21NOV20:01:44  a  26 1
21NOV20:01:49  a  28 1
21NOV20:01:54  a  27 1
21NOV20:01:59  a  26 1
21NOV20:02:04  a  25 1
21NOV20:02:09  a  24 1
21NOV20:02:14  a  23 1
21NOV20:02:19  a  21 1
21NOV20:02:24  a  22 1
21NOV20:02:29  a  18 1
21NOV20:02:34  a  20 1
21NOV20:02:39  a  27 1
21NOV20:02:44  a  29 1
21NOV20:02:49  a  32 .
21NOV20:02:54  a  31 .
21NOV20:02:59  a  32 .
21NOV20:03:04  a  29 .
21NOV20:03:09  a  29 .
21NOV20:03:14  a  29 2
21NOV20:03:19  a  28 2
21NOV20:03:24  a  28 2
21NOV20:03:29  a  29 2
21NOV20:03:34  a  28 2
;;;;
   run;
proc print;
   run;

Re: retain and count

linlin87 — Thu, 03 Apr 2025 17:00:31 GMT

Thank you making SAS data. It is less than or equal 30%.

Re: retain and count

data_null__ — Thu, 03 Apr 2025 17:26:39 GMT

@linlin87 wrote:

Thank you making SAS data. It is less than or equal 30%.

You need to address @PaigeMiller question.

How can row 5 show (RH Drop Number) = 1 since there have not been "3 consecutive datapoint in humidity below a threshold (30%)" ???

You are not giving enough information for me to understand the problem.

Re: retain and count

linlin87 — Thu, 03 Apr 2025 17:33:16 GMT

At row 5, there are 3 consecutive datapoint. 30 (row 3), 30 (row 4) and then the current row, which is 29 (row 5).

Re: retain and count

linlin87 — Thu, 03 Apr 2025 17:34:40 GMT

To be a drop in RH above noise it need 3 datapoint below 30, and to be end of drop in RH it need 3 datapoint out of 30. Hope make sense now?

Re: retain and count

data_null__ — Thu, 03 Apr 2025 19:16:48 GMT

@linlin87 wrote:

To be a drop in RH above noise it need 3 datapoint below 30, and to be end of drop in RH it need 3 datapoint out of 30. Hope make sense now?

Let me know if I understand correctly.

data rh;
   input datetime :datetime.  loc $  RH RHDrop;
   format datetime datetime.;
   length DropFl 8;
   fl = rh le 30;
   if fl 
      then seq + 1;
      else seq = .;
   if seq eq 3 then TempDropFl + 1;
   if not fl 
      then DropFl = .;
      else if seq ge 3 then DropFL = TempDropFl;
   *drop fl seq Temp:;
   cards;
21NOV20:00:59  a  33 .
21NOV20:01:04  a  31 .
21NOV20:01:09  a  30 .
21NOV20:01:14  a  30 .
21NOV20:01:19  a  29 1
21NOV20:01:24  a  28 1
21NOV20:01:29  a  27 1
21NOV20:01:34  a  27 1
21NOV20:01:39  a  27 1
21NOV20:01:44  a  26 1
21NOV20:01:49  a  28 1
21NOV20:01:54  a  27 1
21NOV20:01:59  a  26 1
21NOV20:02:04  a  25 1
21NOV20:02:09  a  24 1
21NOV20:02:14  a  23 1
21NOV20:02:19  a  21 1
21NOV20:02:24  a  22 1
21NOV20:02:29  a  18 1
21NOV20:02:34  a  20 1
21NOV20:02:39  a  27 1
21NOV20:02:44  a  29 1
21NOV20:02:49  a  32 .
21NOV20:02:54  a  31 .
21NOV20:02:59  a  32 .
21NOV20:03:04  a  29 .
21NOV20:03:09  a  29 .
21NOV20:03:14  a  29 2
21NOV20:03:19  a  28 2
21NOV20:03:24  a  28 2
21NOV20:03:29  a  29 2
21NOV20:03:34  a  28 2
;;;;
   run;
proc print;
   run;

Re: retain and count

linlin87 — Thu, 03 Apr 2025 19:36:57 GMT

This is what it is, thank you @data_null__ you awesome!

Please you explain how it work line-by-line to me?

I ask more please. How can I also say that I need the 3 consecutive points within 15 mins (datetime has some variation).

Do I RETAIN datetime and edit your line of code as this?

   if seq eq 3 and lag2(datetime) le 15 then TempDropFl + 1;

Re: retain and count

linlin87 — Thu, 03 Apr 2025 19:38:21 GMT

Sorry this

 if seq eq 3 and (datetime-lag2(datetime)) le 15 then TempDropFl + 1;

Re: retain and count

linlin87 — Thu, 03 Apr 2025 19:39:39 GMT

Also this work BY loc? Starting DropFL from 1 on new loc? I try on data.

Re: retain and count

linlin87 — Fri, 04 Apr 2025 09:37:27 GMT

I now test this and it perform good. But one problem I really appreciate your help with @data_null__ . When you have the end of the drop, if within three values you get another fl=1 then it must continue to label the drop. See orange below and "Want". I have given this data so you can try.

I try edit code but no success so very grateful and want to see how you do.

datetime	loc	RH	temp	fl	seq	TempDropFl	DropFl	Want
30/11/2024 10:37	Nuneaton	35	.	0	.	4	.
30/11/2024 10:42	Nuneaton	37	.	0	.	4	.
30/11/2024 10:47	Nuneaton	31	.	0	.	4	.
30/11/2024 10:52	Nuneaton	32	.	0	.	4	.
30/11/2024 10:57	Nuneaton	32	.	0	.	4	.
30/11/2024 11:02	Nuneaton	28	.	1	1	4	.
30/11/2024 11:07	Nuneaton	36	.	0	.	4	.
30/11/2024 11:12	Nuneaton	33	.	0	.	4	.
30/11/2024 11:18	Nuneaton	32	.	0	.	4	.
30/11/2024 11:23	Nuneaton	38	.	0	.	4	.
30/11/2024 11:28	Nuneaton	30	.	1	1	4	.
30/11/2024 11:33	Nuneaton	28	.	1	2	4	.
30/11/2024 11:38	Nuneaton	29	.	1	3	5	5	5
30/11/2024 11:43	Nuneaton	35	.	0	.	5	.	5
30/11/2024 11:48	Nuneaton	32	.	0	.	5	.	5
30/11/2024 11:53	Nuneaton	30	.	1	1	5	.	5
30/11/2024 11:58	Nuneaton	32	.	0	.	5	.
30/11/2024 12:03	Nuneaton	33	.	0	.	5	.
30/11/2024 12:08	Nuneaton	33	.	0	.	5	.

Re: retain and count

linlin87 — Tue, 29 Apr 2025 08:40:09 GMT

Maybe SAS is not good for doing this type of tasks? Do you recommend different platform @data_null__ for doing this difficult label of events in timeseries? I very happy to listen to recommendation. Would SAS Viya handle better?

Re: retain and count

data_null__ — Tue, 29 Apr 2025 12:50:55 GMT

@linlin87 sorry I was not able to offer any further input. Perhaps if you ask the question again some of the better programmers can help you figure this.

Re: retain and count

PaigeMiller — Tue, 29 Apr 2025 14:53:09 GMT

@linlin87 wrote:

Maybe SAS is not good for doing this type of tasks? Do you recommend different platform @data_null__ for doing this difficult label of events in timeseries? I very happy to listen to recommendation. Would SAS Viya handle better?

SAS Viya is essentially SAS 9.4 with many additional features and greater computational power; but this is a programming issue, not a platform issue, not a feature issue. You, or someone, needs to figure out the right method of programming to achieve the desired results, and then I'm sure SAS can do the job.

Re: retain and count

ballardw — Tue, 29 Apr 2025 20:57:09 GMT

@linlin87 wrote:

Dear SAS New User,

Please can help with this problem, it trying to detect and enumerate drops in relative humidity (RH) by geographical location. The drop needs enumerating for later analysis.

Defining drop in RH follows rules:
1) to be a drop in RH it must have 3 consecutive datapoint in humidity below a threshold (less than or equal 30%).
2) The drop in humidity finishes stop if there are 3 consecutive points above threshold (31% and above).
3) Then on the next started drop (within that location), it labels drop as +1.

I would say that you need to reconsider your rules a bit. I suspect this has more that a bit of "boundary value problem" behavior, meaning that the boundaries of the "drop" and end intervals have not been clearly enough defined. For example you are saying "threshold (31% and above)" which leaves a gap between the less than or equal to 30. You shown data may not indicate any such but Relative Humidity is a continuous measurement and values like 30.5 need to be accounted for in the programming. OR you need to explicitly state that you have only integer values.

Also you have no rules addressing missing values. What would you do if you have an hour with no value?

Or what if sequential Datetime values are excessive, such as 2 hours between measures for the same location?

I'm not sure that your rules clearly define what the result should look like with values like

BTW I would not call this a "drop", but a "below threshold". Short hand names can create confusing statements in a narrative when it is hard to tell whether a "variable" or "data value" or external physical process might be meant. A value reduction from 32 to 31 would be considered a "drop" by most people if discussing numeric values where as value change from 28 to 29 would be considered an increase and not a "drop" in any form.

What about RH less than 0 or greater than 100? Theoretically shouldn't occur but I have seen instrument reading outside that range. I've also had instruments that missed reporting intervals because of failure or maintenance. So I am used to considering the time between measurements as important for any such change of values.