Fluorite | Level 6

## calculate time difference in one column

Hi, Appreciate any help for this. how to get NRS score 8 for timepoint 1 and medication O with SAS?

 subject timepoint(hour) time of assessment NRS score medication want output(NRS score) 101-0011 0 09:00:59 6 101-0011 1 11:13:00 3 101-0011 1 11:21:00 8 O 8  (11:21:00+4:00:00=15:21:00  all time of assessment below this row and happened before 15:21:00 are consider as timepoint 1 group. pick up the maximum NRS score 8 as output ) 101-0011 2 12:13:00 7 101-0011 4 13:59:59 6 O 101-0011 4 14:20:00 8 101_0011 6 16:10:33 9
1 ACCEPTED SOLUTION

Accepted Solutions
Onyx | Level 15

## Re: calculate time difference in one column

I did my best guess:

``````data have;
infile cards missover;
input subject : \$ 20. timepoint time_of_assessment time8. NRS_score medication \$;
format time_of_assessment time.;
cards;
101-0011 0 09:00:59 6
101-0011 1 11:13:00 3
101-0011 1 11:21:00 8 O
101-0011 2 12:13:00 7
101-0011 4 13:59:59 6 O
101-0011 4 14:20:00 8
101-0011 6 16:10:33 9
101-0012 0 09:00:59 6
101-0012 1 11:13:00 3
101-0012 1 11:21:00 8 O
101-0012 2 12:13:00 7
101-0012 4 17:59:59 6 O
101-0012 4 18:20:00 1
101-0012 6 19:10:33 3
;
run;
proc print;
run;

data have1;
set have;
where medication="O";

rangelow = 0;
rangeup  = time_of_assessment + 4*60*60;

keep subject time_of_assessment medication rangelow rangeup;
run;
proc print;
run;

proc sql;
create table have2 as
select a.subject, a.time_of_assessment, max(b.NRS_score) as max_score
from have1 as a, have as b
where a.subject = b.subject
and b.time_of_assessment between a.rangelow and a.rangeup
group by a.subject, a.time_of_assessment
order by a.subject, a.time_of_assessment
;
quit;
proc print;
run;

data want;
merge have have2;
by subject time_of_assessment;
run;
proc print;
run;``````

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation

13 REPLIES 13
Onyx | Level 15

## Re: calculate time difference in one column

Could you explain the logic behind what you want to achieve?

Why record for 11:21:00 is more "special" than the one form 13:59:59 (which also have "O")?

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation

Fluorite | Level 6

## Re: calculate time difference in one column

Hi@yabwon, the data I got from my boss. I picked up parts of the raw data. the second "O" is for another time point.

Onyx | Level 15

## Re: calculate time difference in one column

Sorry for auto-quoting but: "Could you explain the logic behind what you want to achieve?"

B.

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation

Fluorite | Level 6

## Re: calculate time difference in one column

Hi @yabwon ,

I hope you understand my explanation. for timepoint 1 and medication A: assessment time 11:21:00+4:00:00=15:21:00,  all times of assessment below this row and happened before 15:21:00 are considered as timepoint 1 group. pick up the maximum NRS score 8 in this group as output

Thank you

Diamond | Level 26

## Re: calculate time difference in one column

I was going to ask: then why don't we created wanted output for row 6, which also has medication O? I think this is what @yabwon  wants to know as well.

But I am not going to ask that. We have reached a point, just like in your last thread, where we have to ask 20 questions to get a clear understanding of the problem. This is not a good way to get the help you need.

@sophielr — would you please write a complete and clear explanation of the entire problem? Please do that rather than us asking question and follow-up question and another follow-up question. Emphasis on COMPLETE. Emphasis on CLEAR.  Emphasis on COMPLETE. Emphasis on COMPLETE. Emphasis on COMPLETE.

--
Paige Miller
Fluorite | Level 6

## Re: calculate time difference in one column

Hi @PaigeMiller,

yes, I need to calculate the row 6 score also. I want to make my question easy so I will do the next part if I know how to calculate the first one.

Diamond | Level 26

## Re: calculate time difference in one column

@sophielr wrote:

Hi @PaigeMiller,

yes, I need to calculate the row 6 score also. I want to make my question easy so I will do the next part if I know how to calculate the first one.

I don't want answers to the specific questions that have been asked. I have already said that I want a COMPLETE and CLEAR explanation of the problem so we don't have to keep asking more questions, and so we don't have to keep scrolling up and down to understand your answers and how they relate to the data.

--
Paige Miller
Onyx | Level 15

## Re: calculate time difference in one column

You wan to "see into the future" for 4 hours, what about "the past"? is it "select all before" or maybe there is some limitation ?

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation

Fluorite | Level 6

## Re: calculate time difference in one column

Hi @yabwon ,

I do not need to consider "the past time" right now. I only want to know how to find the rows in the calculated range( recorded time +4hours) and then find maximum number in this range.

Thank you

Onyx | Level 15

## Re: calculate time difference in one column

I did my best guess:

``````data have;
infile cards missover;
input subject : \$ 20. timepoint time_of_assessment time8. NRS_score medication \$;
format time_of_assessment time.;
cards;
101-0011 0 09:00:59 6
101-0011 1 11:13:00 3
101-0011 1 11:21:00 8 O
101-0011 2 12:13:00 7
101-0011 4 13:59:59 6 O
101-0011 4 14:20:00 8
101-0011 6 16:10:33 9
101-0012 0 09:00:59 6
101-0012 1 11:13:00 3
101-0012 1 11:21:00 8 O
101-0012 2 12:13:00 7
101-0012 4 17:59:59 6 O
101-0012 4 18:20:00 1
101-0012 6 19:10:33 3
;
run;
proc print;
run;

data have1;
set have;
where medication="O";

rangelow = 0;
rangeup  = time_of_assessment + 4*60*60;

keep subject time_of_assessment medication rangelow rangeup;
run;
proc print;
run;

proc sql;
create table have2 as
select a.subject, a.time_of_assessment, max(b.NRS_score) as max_score
from have1 as a, have as b
where a.subject = b.subject
and b.time_of_assessment between a.rangelow and a.rangeup
group by a.subject, a.time_of_assessment
order by a.subject, a.time_of_assessment
;
quit;
proc print;
run;

data want;
merge have have2;
by subject time_of_assessment;
run;
proc print;
run;``````

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation

Fluorite | Level 6

## Re: calculate time difference in one column

Thank you @yabwon, you give me a big help.

Diamond | Level 26

## Re: calculate time difference in one column

11:21:00+4:00:00=15:21:00  all time of assessment below this row and happened before 15:21:00

Where does the 4 hours come from?

What would happen if row 6 at time 14:20:00 had an NRS Score of 10? Would then the wanted output on row 3 be 10?

--
Paige Miller
Fluorite | Level 6

## Re: calculate time difference in one column

Hi @PaigeMiller ,

4 hours is the criteria according to this trial document. the wanted score should be 10 if row 6 at time 14:20:00 had an NRS Score of 10.

Discussion stats
• 13 replies
• 2135 views
• 1 like
• 3 in conversation