Thank you for any help for my question. I have to get red number. '6' is the maximum value which time of assessment recorded before 11:21:00. '8' is the maximum value which time of assessment recorded before 13:59:59. I do not know how to get '6' and '8' by SAS.
ubject | 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 | 6 |
101-0011 | 2 | 12:13:00 | 7 | ||
101-0011 | 4 | 13:59:59 | 6 | O | 8 |
101-0011 | 4 | 14:20:00
| 8 | ||
101_0011 | 6 | 16:10:33 | 9 |
I would said: "Take the solution from yesterday and put some effort..."
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-1;
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;
I would said: "Take the solution from yesterday and put some effort..."
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-1;
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;
Appreciate, @yabwon . You help me again.
I don't follow your description, but your data makes it look like you want the maximum score BEFORE the observation that has MEDICATION='O'. So just use a retained variable to capture the maximum score.
data have;
input Subject $ hour time :time. score medication $ want ;
format time tod8. ;
cards;
101-0011 0 09:00:59 6 . .
101-0011 1 11:13:00 3 . .
101-0011 1 11:21:00 8 O 6
101-0011 2 12:13:00 7 . .
101-0011 4 13:59:59 6 O 8
101-0011 4 14:20:00 8 . .
101_0011 6 16:10:33 9 . .
;
data want ;
set have;
by subject ;
if first.subject then max=.;
retain max;
if medication='O' then nrs=max;
max=max(max,score);
run;
OBS Subject hour time score medication want max nrs 1 101-0011 0 09:00:59 6 . 6 . 2 101-0011 1 11:13:00 3 . 6 . 3 101-0011 1 11:21:00 8 O 6 8 6 4 101-0011 2 12:13:00 7 . 8 . 5 101-0011 4 13:59:59 6 O 8 8 8 6 101-0011 4 14:20:00 8 . 8 . 7 101_0011 6 16:10:33 9 . 9 .
@Tom ,
I based my solution on the one I gave for related question in this thread: https://communities.sas.com/t5/SAS-Programming/calculate-time-difference-in-one-column/td-p/871588
but I want to say that if it's about today question I like yours much more!
Bart
Thank you, Tom. You show me a new method.
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.