- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 |
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you @yabwon, you give me a big help.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.