Please, help me to solve the problem.
I have a "time" column in a table. I need to fill in the Marker column in the table.
The first line - "Marker" is equal to one.
Then, if more than ONE HOUR has passed since the last moment, the marker should increase by one. And so on until the end of the table.
I will be extremely grateful to see the code with the solution!
time | marker | |
14:01 | 1 | |
14:02 | 1 | |
14:03 | 1 | |
14:04 | 1 | |
14:05 | 1 | |
14:06 | 1 | |
17:33 | 2 | here I need to increase "marker" (because more them 1 hour pass) |
17:34 | 2 | |
17:35 | 2 | |
19:38 | 3 | here I need to increase "marker" (because more them 1 hour pass) |
19:39 | 3 | |
19:40 | 3 | |
19:41 | 3 |
data want;
set have;
retain marker _time;
if _n_ = 1
then do;
marker = 1;
_time = time;
end;
if intck('hour',_time,time,'c') >= 1
then do;
_time = time;
marker + 1;
end;
drop _time;
run;
For code working and tested with your data, please post your data in a data step with datalines.
Edit: added missing single quote in INTCK function.
data want;
set have;
retain marker _time;
if _n_ = 1
then do;
marker = 1;
_time = time;
end;
if intck('hour',_time,time,'c') >= 1
then do;
_time = time;
marker + 1;
end;
drop _time;
run;
For code working and tested with your data, please post your data in a data step with datalines.
Edit: added missing single quote in INTCK function.
@Ksharp wrote:
Kurt,
You missed a single quote.
if intck('hour,_time,time,'c')
-->
if intck( 'hour' , _time , time ,'c')
Thanks! Corrected.
Hi,
Something like this:
data have;
input time time.;
format time time9.;
cards;
14:01
14:02
14:03
14:04
14:05
14:06
17:33
17:34
17:35
19:38
19:39
19:40
19:41
20:20
20:21
23:04
23:05
00:16
00:17
01:34
;
run;
data want;
set have;
retain marker 1;
if abs(coalesce(dif(time),0)) > 3600 then marker + 1;
run;
?
Bart
A slightly simpler version of the code supplied by @yabwon :
data want;
set have;
retain marker 1;
marker+dif(time)>3600;
run;
I think @yabwon was trying to take into account that the time could go past midnight, and that there might be missing values. Except that the missing values are meaningless and should be weeded out (I suppose you do not have any, and your sample show none), and the code he supplied would also give an increase in the marker from e.g. one minute before midnight to one minute after.
If you want to take time values before/after midnight into consideration, I think you should switch to using datetime values instead of time values (the code is basically the same, as SAS internally represents both types as a number of seconds).
The other little difference is that the IF/THEN construct is replaced by just adding the logical value, which in SAS is 1 or 0. As humans we tend to look first and then act if necessary - for the computer it is often just as fast to just act, especially when the action is very simple, such as adding 1 or 0 to a number.
Normally, you would have other variables on the dataset, e.g. a patient ID, for which you would want to restart the marker count. Assuming your key variable is ID, that can be accomplished like this:
data want;
set have;
by ID;
marker+dif(time)>3600;
if first.ID then marker=1;
run;
It looks a bit strange that I first calculate a marker, and then reset it to 1 when the ID changes. The reason is that the DIF function must be called in for every observation, else things go awry.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.