Hello,
I have a dataset that Looks like this:
I would like to assign an increasing number from 1 to 7 to a new variable when "Time_start_for" change (increase) and:
-ID is the same
-date_for is the same
when is done should start again with 1.
To explain me better: every ID (test subject) has on different Dates 7 different "sessions".
The "Time_start_for" is not always the same for all the subjects.
What I want is a new variable that indicate the session number.
Thank you!
Version: 9.4
So this does it:
data have;
input ID Date_for:mmddyy10. Time_start_for :time5.;
infile datalines dlm=',';
format Date_for mmddyy10. Time_start_for time5.;
datalines;
101,01/31/19,10:19
101,01/31/19,10:19
101,01/31/19,10:19
101,01/31/19,10:19
101,01/31/19,10:19
101,01/31/19,11:58
101,01/31/19,11:58
101,01/31/19,11:58
101,01/31/19,11:58
101,01/31/19,11:58
101,01/31/19,13:00
101,01/31/19,13:00
101,01/31/19,13:00
101,01/31/19,13:00
101,01/31/19,13:00
101,01/31/19,14:00
101,01/31/19,14:00
101,01/31/19,14:00
101,01/31/19,14:00
101,01/31/19,14:00
101,01/31/19,15:01
101,01/31/19,15:01
101,01/31/19,15:01
101,01/31/19,15:01
101,01/31/19,15:01
101,01/31/19,15:58
101,01/31/19,15:58
101,01/31/19,15:58
101,01/31/19,15:58
101,01/31/19,15:58
101,01/31/19,16:59
101,01/31/19,16:59
101,01/31/19,16:59
101,01/31/19,16:59
101,01/31/19,16:59
101,02/07/19,9:34
101,02/07/19,9:34
101,02/07/19,9:34
101,02/07/19,9:34
101,02/07/19,9:34
101,02/07/19,11:00
101,02/07/19,11:00
101,02/07/19,11:00
101,02/07/19,11:00
101,02/07/19,11:00
101,02/07/19,11:57
101,02/07/19,11:57
101,02/07/19,11:57
101,02/07/19,11:57
101,02/07/19,11:57
101,02/07/19,12:58
101,02/07/19,12:58
101,02/07/19,12:58
101,02/07/19,12:58
101,02/07/19,12:58
;
run;
data want;
set have;
by id date_for time_start_for;
if first.date_for then session = 0;
if first.time_start_for then session + 1;
run;
proc print data=want noobs;
run;
Result:
Time_ start_ ID Date_for for session 101 01/31/2019 10:19 1 101 01/31/2019 10:19 1 101 01/31/2019 10:19 1 101 01/31/2019 10:19 1 101 01/31/2019 10:19 1 101 01/31/2019 11:58 2 101 01/31/2019 11:58 2 101 01/31/2019 11:58 2 101 01/31/2019 11:58 2 101 01/31/2019 11:58 2 101 01/31/2019 13:00 3 101 01/31/2019 13:00 3 101 01/31/2019 13:00 3 101 01/31/2019 13:00 3 101 01/31/2019 13:00 3 101 01/31/2019 14:00 4 101 01/31/2019 14:00 4 101 01/31/2019 14:00 4 101 01/31/2019 14:00 4 101 01/31/2019 14:00 4 101 01/31/2019 15:01 5 101 01/31/2019 15:01 5 101 01/31/2019 15:01 5 101 01/31/2019 15:01 5 101 01/31/2019 15:01 5 101 01/31/2019 15:58 6 101 01/31/2019 15:58 6 101 01/31/2019 15:58 6 101 01/31/2019 15:58 6 101 01/31/2019 15:58 6 101 01/31/2019 16:59 7 101 01/31/2019 16:59 7 101 01/31/2019 16:59 7 101 01/31/2019 16:59 7 101 01/31/2019 16:59 7 101 02/07/2019 9:34 1 101 02/07/2019 9:34 1 101 02/07/2019 9:34 1 101 02/07/2019 9:34 1 101 02/07/2019 9:34 1 101 02/07/2019 11:00 2 101 02/07/2019 11:00 2 101 02/07/2019 11:00 2 101 02/07/2019 11:00 2 101 02/07/2019 11:00 2 101 02/07/2019 11:57 3 101 02/07/2019 11:57 3 101 02/07/2019 11:57 3 101 02/07/2019 11:57 3 101 02/07/2019 11:57 3 101 02/07/2019 12:58 4 101 02/07/2019 12:58 4 101 02/07/2019 12:58 4 101 02/07/2019 12:58 4 101 02/07/2019 12:58 4
Can you provide your data in a usable form?
Makes it much easier to provide a usable code answer 🙂
ID,Date_for,Time_start_for,_TYPE_,_FREQ_,_STAT_,HR,Atemfrequenz,SpO2 101,01/31/19,10:19,0,118,N,118,118,118 101,01/31/19,10:19,0,118,MIN,81.5,7.5757575758,95 101,01/31/19,10:19,0,118,MAX,96.25,27.649769585,96 101,01/31/19,10:19,0,118,MEAN,88.846559726,14.766582398,95.156920904 101,01/31/19,10:19,0,118,STD,2.7743979079,2.7931480556,0.305087647 101,01/31/19,11:58,0,140,N,140,140,140 101,01/31/19,11:58,0,140,MIN,75,6.8259385666,89.333333333 101,01/31/19,11:58,0,140,MAX,93,23.346303502,92 101,01/31/19,11:58,0,140,MEAN,83.328511905,17.71234626,90.900436508 101,01/31/19,11:58,0,140,STD,3.1957743662,2.8950150452,0.6360118615 101,01/31/19,13:00,0,151,N,151,151,151 101,01/31/19,13:00,0,151,MIN,78.6,7.5949367089,89 101,01/31/19,13:00,0,151,MAX,92.333333333,23.166023166,92.333333333 101,01/31/19,13:00,0,151,MEAN,85.379801325,18.333250653,90.838410596 101,01/31/19,13:00,0,151,STD,2.5256651737,2.8289307891,0.6783282034 101,01/31/19,14:00,0,154,N,154,154,154 101,01/31/19,14:00,0,154,MIN,67.333333333,6.7643742954,91 101,01/31/19,14:00,0,154,MAX,101.5,30,94 101,01/31/19,14:00,0,154,MEAN,87.054298083,19.278342826,92.448953824 101,01/31/19,14:00,0,154,STD,4.145978296,3.1982577136,0.6179428555 101,01/31/19,15:01,0,150,N,150,150,150 101,01/31/19,15:01,0,150,MIN,78,6.8337129841,91 101,01/31/19,15:01,0,150,MAX,99.2,32.786885246,95 101,01/31/19,15:01,0,150,MEAN,84.734404762,18.504781387,92.669777778 101,01/31/19,15:01,0,150,STD,3.2353262727,3.5150039626,0.7165649131 101,01/31/19,15:58,0,159,N,159,159,159 101,01/31/19,15:58,0,159,MIN,80,9.7879282219,90 101,01/31/19,15:58,0,159,MAX,92,23.529411765,94 101,01/31/19,15:58,0,159,MEAN,86.893186583,19.174924095,91.981551363 101,01/31/19,15:58,0,159,STD,2.496592061,2.3915392137,0.7136098857 101,01/31/19,16:59,0,154,N,154,154,154 101,01/31/19,16:59,0,154,MIN,80.5,6.528835691,92 101,01/31/19,16:59,0,154,MAX,98.333333333,24.291497976,96 101,01/31/19,16:59,0,154,MEAN,88.338899196,19.373919748,93.797186147 101,01/31/19,16:59,0,154,STD,2.6253857217,3.1071420356,0.8541002927 101,02/07/19,9:34,0,138,N,138,138,138 101,02/07/19,9:34,0,138,MIN,83,7.4074074074,96.75 101,02/07/19,9:34,0,138,MAX,96.5,23.255813953,98.666666667 101,02/07/19,9:34,0,138,MEAN,89.338112491,17.429150926,97.667011732 101,02/07/19,9:34,0,138,STD,2.3489566457,3.1713217584,0.4528912065 101,02/07/19,11:00,0,145,N,145,145,145 101,02/07/19,11:00,0,145,MIN,79.666666667,6.0975609756,88 101,02/07/19,11:00,0,145,MAX,90.333333333,23.166023166,92.75 101,02/07/19,11:00,0,145,MEAN,85.648259442,17.757981771,89.848604269 101,02/07/19,11:00,0,145,STD,2.4946143529,2.9558597284,0.8604079029 101,02/07/19,11:57,0,149,N,149,149,149 101,02/07/19,11:57,0,149,MIN,74.666666667,9.2592592593,89 101,02/07/19,11:57,0,149,MAX,86.5,25.210084034,93.666666667 101,02/07/19,11:57,0,149,MEAN,82.228427613,18.242657167,91.121428571 101,02/07/19,11:57,0,149,STD,2.1273408499,3.0168947674,0.9109684751 101,02/07/19,12:58,0,141,N,141,141,141 101,02/07/19,12:58,0,141,MIN,77,5.1063829787,89 101,02/07/19,12:58,0,141,MAX,91,34.88372093,93 101,02/07/19,12:58,0,141,MEAN,84.177090049,18.019936177,91.111583924 101,02/07/19,12:58,0,141,STD,2.9756893198,3.593121113,0.8299501402 101,02/07/19,13:58,0,135,N,135,135,135 101,02/07/19,13:58,0,135,MIN,79,7.3619631902,88.75 101,02/07/19,13:58,0,135,MAX,92,26.200873362,93 101,02/07/19,13:58,0,135,MEAN,85.887460317,16.609089431,89.862345679 101,02/07/19,13:58,0,135,STD,2.3950279785,2.9043491671,0.902890519 101,02/07/19,14:55,0,142,N,142,142,142 101,02/07/19,14:55,0,142,MIN,83.25,8.1190798376,89 101,02/07/19,14:55,0,142,MAX,96.75,21.428571429,93.5 101,02/07/19,14:55,0,142,MEAN,89.697065728,17.453225263,90.860160966 101,02/07/19,14:55,0,142,STD,2.7399597438,2.8494281241,0.6730471505 101,02/07/19,15:59,0,144,N,144,144,144 101,02/07/19,15:59,0,144,MIN,78.5,9.6,89 101,02/07/19,15:59,0,144,MAX,90.666666667,22.556390977,93 101,02/07/19,15:59,0,144,MEAN,85.147106481,17.402927058,90.960532407 101,02/07/19,15:59,0,144,STD,2.5681513657,2.46291814,0.8412646956 126,02/01/19,9:51,0,96,N,96,96,96 126,02/01/19,9:51,0,96,MIN,67.6,7.100591716,95 126,02/01/19,9:51,0,96,MAX,83,22.900763359,97 126,02/01/19,9:51,0,96,MEAN,72.25536541,12.918101228,95.826116071 126,02/01/19,9:51,0,96,STD,2.6607553354,3.5511741217,0.6422341224 126,02/01/19,11:26,0,129,N,129,129,129 126,02/01/19,11:26,0,129,MIN,78,7.2115384615,65.6 126,02/01/19,11:26,0,129,MAX,97,32.432432432,80.666666667 126,02/01/19,11:26,0,129,MEAN,86.562575366,17.138618253,72.190079365 126,02/01/19,11:26,0,129,STD,4.4198323625,5.1557816279,3.2852246717 126,02/01/19,12:25,0,120,N,120,120,120 126,02/01/19,12:25,0,120,MIN,79.5,7.371007371,68 126,02/01/19,12:25,0,120,MAX,99.142857143,27.52293578,77.333333333
Like this is ok? 🙂
I made a SAS data set from this. So your data looks like below.
Does each line correspond to a 'session'? If so, I see more than 7 sessions for ID=101 on the date 01/31/19?
data have;
input ID Date_for:mmddyy10. Time_start_for $ _TYPE_ _FREQ_ _STAT_ $ HR Atemfrequenz SpO2;
infile datalines dlm=',';
format Date_for mmddyy10.;
datalines;
101,01/31/19,10:19,0,118,N,118,118,118
101,01/31/19,10:19,0,118,MIN,81.5,7.5757575758,95
101,01/31/19,10:19,0,118,MAX,96.25,27.649769585,96
101,01/31/19,10:19,0,118,MEAN,88.846559726,14.766582398,95.156920904
101,01/31/19,10:19,0,118,STD,2.7743979079,2.7931480556,0.305087647
101,01/31/19,11:58,0,140,N,140,140,140
101,01/31/19,11:58,0,140,MIN,75,6.8259385666,89.333333333
101,01/31/19,11:58,0,140,MAX,93,23.346303502,92
101,01/31/19,11:58,0,140,MEAN,83.328511905,17.71234626,90.900436508
101,01/31/19,11:58,0,140,STD,3.1957743662,2.8950150452,0.6360118615
101,01/31/19,13:00,0,151,N,151,151,151
101,01/31/19,13:00,0,151,MIN,78.6,7.5949367089,89
101,01/31/19,13:00,0,151,MAX,92.333333333,23.166023166,92.333333333
101,01/31/19,13:00,0,151,MEAN,85.379801325,18.333250653,90.838410596
101,01/31/19,13:00,0,151,STD,2.5256651737,2.8289307891,0.6783282034
101,01/31/19,14:00,0,154,N,154,154,154
101,01/31/19,14:00,0,154,MIN,67.333333333,6.7643742954,91
101,01/31/19,14:00,0,154,MAX,101.5,30,94
101,01/31/19,14:00,0,154,MEAN,87.054298083,19.278342826,92.448953824
101,01/31/19,14:00,0,154,STD,4.145978296,3.1982577136,0.6179428555
101,01/31/19,15:01,0,150,N,150,150,150
101,01/31/19,15:01,0,150,MIN,78,6.8337129841,91
101,01/31/19,15:01,0,150,MAX,99.2,32.786885246,95
101,01/31/19,15:01,0,150,MEAN,84.734404762,18.504781387,92.669777778
101,01/31/19,15:01,0,150,STD,3.2353262727,3.5150039626,0.7165649131
101,01/31/19,15:58,0,159,N,159,159,159
101,01/31/19,15:58,0,159,MIN,80,9.7879282219,90
101,01/31/19,15:58,0,159,MAX,92,23.529411765,94
101,01/31/19,15:58,0,159,MEAN,86.893186583,19.174924095,91.981551363
101,01/31/19,15:58,0,159,STD,2.496592061,2.3915392137,0.7136098857
101,01/31/19,16:59,0,154,N,154,154,154
101,01/31/19,16:59,0,154,MIN,80.5,6.528835691,92
101,01/31/19,16:59,0,154,MAX,98.333333333,24.291497976,96
101,01/31/19,16:59,0,154,MEAN,88.338899196,19.373919748,93.797186147
101,01/31/19,16:59,0,154,STD,2.6253857217,3.1071420356,0.8541002927
101,02/07/19,9:34,0,138,N,138,138,138
101,02/07/19,9:34,0,138,MIN,83,7.4074074074,96.75
101,02/07/19,9:34,0,138,MAX,96.5,23.255813953,98.666666667
101,02/07/19,9:34,0,138,MEAN,89.338112491,17.429150926,97.667011732
101,02/07/19,9:34,0,138,STD,2.3489566457,3.1713217584,0.4528912065
101,02/07/19,11:00,0,145,N,145,145,145
101,02/07/19,11:00,0,145,MIN,79.666666667,6.0975609756,88
101,02/07/19,11:00,0,145,MAX,90.333333333,23.166023166,92.75
101,02/07/19,11:00,0,145,MEAN,85.648259442,17.757981771,89.848604269
101,02/07/19,11:00,0,145,STD,2.4946143529,2.9558597284,0.8604079029
101,02/07/19,11:57,0,149,N,149,149,149
101,02/07/19,11:57,0,149,MIN,74.666666667,9.2592592593,89
101,02/07/19,11:57,0,149,MAX,86.5,25.210084034,93.666666667
101,02/07/19,11:57,0,149,MEAN,82.228427613,18.242657167,91.121428571
101,02/07/19,11:57,0,149,STD,2.1273408499,3.0168947674,0.9109684751
101,02/07/19,12:58,0,141,N,141,141,141
101,02/07/19,12:58,0,141,MIN,77,5.1063829787,89
101,02/07/19,12:58,0,141,MAX,91,34.88372093,93
101,02/07/19,12:58,0,141,MEAN,84.177090049,18.019936177,91.111583924
101,02/07/19,12:58,0,141,STD,2.9756893198,3.593121113,0.8299501402
101,02/07/19,13:58,0,135,N,135,135,135
101,02/07/19,13:58,0,135,MIN,79,7.3619631902,88.75
101,02/07/19,13:58,0,135,MAX,92,26.200873362,93
101,02/07/19,13:58,0,135,MEAN,85.887460317,16.609089431,89.862345679
101,02/07/19,13:58,0,135,STD,2.3950279785,2.9043491671,0.902890519
101,02/07/19,14:55,0,142,N,142,142,142
101,02/07/19,14:55,0,142,MIN,83.25,8.1190798376,89
101,02/07/19,14:55,0,142,MAX,96.75,21.428571429,93.5
101,02/07/19,14:55,0,142,MEAN,89.697065728,17.453225263,90.860160966
101,02/07/19,14:55,0,142,STD,2.7399597438,2.8494281241,0.6730471505
101,02/07/19,15:59,0,144,N,144,144,144
101,02/07/19,15:59,0,144,MIN,78.5,9.6,89
101,02/07/19,15:59,0,144,MAX,90.666666667,22.556390977,93
101,02/07/19,15:59,0,144,MEAN,85.147106481,17.402927058,90.960532407
101,02/07/19,15:59,0,144,STD,2.5681513657,2.46291814,0.8412646956
126,02/01/19,9:51,0,96,N,96,96,96
126,02/01/19,9:51,0,96,MIN,67.6,7.100591716,95
126,02/01/19,9:51,0,96,MAX,83,22.900763359,97
126,02/01/19,9:51,0,96,MEAN,72.25536541,12.918101228,95.826116071
126,02/01/19,9:51,0,96,STD,2.6607553354,3.5511741217,0.6422341224
126,02/01/19,11:26,0,129,N,129,129,129
126,02/01/19,11:26,0,129,MIN,78,7.2115384615,65.6
126,02/01/19,11:26,0,129,MAX,97,32.432432432,80.666666667
126,02/01/19,11:26,0,129,MEAN,86.562575366,17.138618253,72.190079365
126,02/01/19,11:26,0,129,STD,4.4198323625,5.1557816279,3.2852246717
126,02/01/19,12:25,0,120,N,120,120,120
126,02/01/19,12:25,0,120,MIN,79.5,7.371007371,68
126,02/01/19,12:25,0,120,MAX,99.142857143,27.52293578,77.333333333
;
Thank you! No every 5 lines correspond to a session (basically every time "Time_start_for" Changes we have a new session.
And also ID 101 for example has 14 sessions but on 2 different days. On both days the sessions should be marked from 1 to 7.
Hi,
I've made some assumptions, such as the data being sorted in id and date_for order, and come up with the following untested code
data want;
set have;
by id date_for;
session_number + 1;
if last.date_for then
session_number = 0;
run;
Regards,
Amir.
Dear Amir,
Thank you very much for answer. Unfortunately is not what I Need.
The session_number should be the same for lines with same "Time_start_for", Count up to 7 and then start again from 1.
Regards,
Riccardo
How about the following:
data want;
set have;
by id date_for;
if first.date_for then
session_number + 1;
if last.id then
session_number = 0;
run;
Show us the expected numbers for every observation of
data have;
infile datalines dlm=',';
input ID Date_for:mmddyy10.;
format Date_for mmddyy10.;
datalines;
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
101,01/31/19
;
run;
data want;
input ID Date_for:mmddyy10. Time_start_for $ Session;
infile datalines dlm=',';
format Date_for mmddyy10.;
datalines;
101,01/31/19,10:19,1
101,01/31/19,10:19,1
101,01/31/19,10:19,1
101,01/31/19,10:19,1
101,01/31/19,10:19,1
101,01/31/19,11:58,2
101,01/31/19,11:58,2
101,01/31/19,11:58,2
101,01/31/19,11:58,2
101,01/31/19,11:58,2
101,01/31/19,13:00,3
101,01/31/19,13:00,3
101,01/31/19,13:00,3
101,01/31/19,13:00,3
101,01/31/19,13:00,3
101,01/31/19,14:00,4
101,01/31/19,14:00,4
101,01/31/19,14:00,4
101,01/31/19,14:00,4
101,01/31/19,14:00,4
101,01/31/19,15:01,5
101,01/31/19,15:01,5
101,01/31/19,15:01,5
101,01/31/19,15:01,5
101,01/31/19,15:01,5
101,01/31/19,15:58,6
101,01/31/19,15:58,6
101,01/31/19,15:58,6
101,01/31/19,15:58,6
101,01/31/19,15:58,6
101,01/31/19,16:59,7
101,01/31/19,16:59,7
101,01/31/19,16:59,7
101,01/31/19,16:59,7
101,01/31/19,16:59,7
101,02/07/19,9:34,1
101,02/07/19,9:34,1
101,02/07/19,9:34,1
101,02/07/19,9:34,1
101,02/07/19,9:34,1
101,02/07/19,11:00,2
101,02/07/19,11:00,2
101,02/07/19,11:00,2
101,02/07/19,11:00,2
101,02/07/19,11:00,2
101,02/07/19,11:57,3
101,02/07/19,11:57,3
101,02/07/19,11:57,3
101,02/07/19,11:57,3
101,02/07/19,11:57,3
101,02/07/19,12:58,4
101,02/07/19,12:58,4
101,02/07/19,12:58,4
101,02/07/19,12:58,4
101,02/07/19,12:58,4
;
run;
The session has to Change when "Time_start_for" changes so this variable is also required.
So this does it:
data have;
input ID Date_for:mmddyy10. Time_start_for :time5.;
infile datalines dlm=',';
format Date_for mmddyy10. Time_start_for time5.;
datalines;
101,01/31/19,10:19
101,01/31/19,10:19
101,01/31/19,10:19
101,01/31/19,10:19
101,01/31/19,10:19
101,01/31/19,11:58
101,01/31/19,11:58
101,01/31/19,11:58
101,01/31/19,11:58
101,01/31/19,11:58
101,01/31/19,13:00
101,01/31/19,13:00
101,01/31/19,13:00
101,01/31/19,13:00
101,01/31/19,13:00
101,01/31/19,14:00
101,01/31/19,14:00
101,01/31/19,14:00
101,01/31/19,14:00
101,01/31/19,14:00
101,01/31/19,15:01
101,01/31/19,15:01
101,01/31/19,15:01
101,01/31/19,15:01
101,01/31/19,15:01
101,01/31/19,15:58
101,01/31/19,15:58
101,01/31/19,15:58
101,01/31/19,15:58
101,01/31/19,15:58
101,01/31/19,16:59
101,01/31/19,16:59
101,01/31/19,16:59
101,01/31/19,16:59
101,01/31/19,16:59
101,02/07/19,9:34
101,02/07/19,9:34
101,02/07/19,9:34
101,02/07/19,9:34
101,02/07/19,9:34
101,02/07/19,11:00
101,02/07/19,11:00
101,02/07/19,11:00
101,02/07/19,11:00
101,02/07/19,11:00
101,02/07/19,11:57
101,02/07/19,11:57
101,02/07/19,11:57
101,02/07/19,11:57
101,02/07/19,11:57
101,02/07/19,12:58
101,02/07/19,12:58
101,02/07/19,12:58
101,02/07/19,12:58
101,02/07/19,12:58
;
run;
data want;
set have;
by id date_for time_start_for;
if first.date_for then session = 0;
if first.time_start_for then session + 1;
run;
proc print data=want noobs;
run;
Result:
Time_ start_ ID Date_for for session 101 01/31/2019 10:19 1 101 01/31/2019 10:19 1 101 01/31/2019 10:19 1 101 01/31/2019 10:19 1 101 01/31/2019 10:19 1 101 01/31/2019 11:58 2 101 01/31/2019 11:58 2 101 01/31/2019 11:58 2 101 01/31/2019 11:58 2 101 01/31/2019 11:58 2 101 01/31/2019 13:00 3 101 01/31/2019 13:00 3 101 01/31/2019 13:00 3 101 01/31/2019 13:00 3 101 01/31/2019 13:00 3 101 01/31/2019 14:00 4 101 01/31/2019 14:00 4 101 01/31/2019 14:00 4 101 01/31/2019 14:00 4 101 01/31/2019 14:00 4 101 01/31/2019 15:01 5 101 01/31/2019 15:01 5 101 01/31/2019 15:01 5 101 01/31/2019 15:01 5 101 01/31/2019 15:01 5 101 01/31/2019 15:58 6 101 01/31/2019 15:58 6 101 01/31/2019 15:58 6 101 01/31/2019 15:58 6 101 01/31/2019 15:58 6 101 01/31/2019 16:59 7 101 01/31/2019 16:59 7 101 01/31/2019 16:59 7 101 01/31/2019 16:59 7 101 01/31/2019 16:59 7 101 02/07/2019 9:34 1 101 02/07/2019 9:34 1 101 02/07/2019 9:34 1 101 02/07/2019 9:34 1 101 02/07/2019 9:34 1 101 02/07/2019 11:00 2 101 02/07/2019 11:00 2 101 02/07/2019 11:00 2 101 02/07/2019 11:00 2 101 02/07/2019 11:00 2 101 02/07/2019 11:57 3 101 02/07/2019 11:57 3 101 02/07/2019 11:57 3 101 02/07/2019 11:57 3 101 02/07/2019 11:57 3 101 02/07/2019 12:58 4 101 02/07/2019 12:58 4 101 02/07/2019 12:58 4 101 02/07/2019 12:58 4 101 02/07/2019 12:58 4
How about the following. I set up Time_start_for to be numeric rather than character and created check to see if session_number equals Session.
data have;
input ID Date_for:mmddyy10. Time_start_for:time5. Session;
infile datalines dlm=',';
format Date_for mmddyy10.
Time_start_for time5.
;
datalines;
101,01/31/19,10:19,1
101,01/31/19,10:19,1
101,01/31/19,10:19,1
101,01/31/19,10:19,1
101,01/31/19,10:19,1
101,01/31/19,11:58,2
101,01/31/19,11:58,2
101,01/31/19,11:58,2
101,01/31/19,11:58,2
101,01/31/19,11:58,2
101,01/31/19,13:00,3
101,01/31/19,13:00,3
101,01/31/19,13:00,3
101,01/31/19,13:00,3
101,01/31/19,13:00,3
101,01/31/19,14:00,4
101,01/31/19,14:00,4
101,01/31/19,14:00,4
101,01/31/19,14:00,4
101,01/31/19,14:00,4
101,01/31/19,15:01,5
101,01/31/19,15:01,5
101,01/31/19,15:01,5
101,01/31/19,15:01,5
101,01/31/19,15:01,5
101,01/31/19,15:58,6
101,01/31/19,15:58,6
101,01/31/19,15:58,6
101,01/31/19,15:58,6
101,01/31/19,15:58,6
101,01/31/19,16:59,7
101,01/31/19,16:59,7
101,01/31/19,16:59,7
101,01/31/19,16:59,7
101,01/31/19,16:59,7
101,02/07/19,9:34,1
101,02/07/19,9:34,1
101,02/07/19,9:34,1
101,02/07/19,9:34,1
101,02/07/19,9:34,1
101,02/07/19,11:00,2
101,02/07/19,11:00,2
101,02/07/19,11:00,2
101,02/07/19,11:00,2
101,02/07/19,11:00,2
101,02/07/19,11:57,3
101,02/07/19,11:57,3
101,02/07/19,11:57,3
101,02/07/19,11:57,3
101,02/07/19,11:57,3
101,02/07/19,12:58,4
101,02/07/19,12:58,4
101,02/07/19,12:58,4
101,02/07/19,12:58,4
101,02/07/19,12:58,4
;
run;
data want;
set have;
by id date_for time_start_for;
if first.time_start_for then
session_number + 1;
check = (session = session_number);
if last.date_for then
session_number = 0;
run;
Your code:
data want;
set have;
by id date_for time_start_for;
if first.time_start_for then
session_number + 1;
check = (session = session_number);
if last.date_for then
session_number = 0;
run;
creates a wrong result (session = 0) for every last observation of a date_for group.
Reset counters at the beginning of a group.
EDIT: Belay that. I mistook session_number for session. Still this code looks unnecessarily complicated to me.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.