BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
sophielr
Fluorite | Level 6

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.

 

ubjecttimepoint(hour)time of assessmentNRS scoremedicationwant output(NRS score)
101-0011009:00:596  
101-0011111:13:003  
101-0011111:21:008O6
101-0011212:13:007  
101-0011413:59:596O8
101-0011414:20:00

 

8  
101_0011616:10:339  
1 ACCEPTED SOLUTION

Accepted Solutions
yabwon
Onyx | Level 15

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;
_______________
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



View solution in original post

5 REPLIES 5
yabwon
Onyx | Level 15

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;
_______________
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



sophielr
Fluorite | Level 6

Appreciate, @yabwon . You help me again.

Tom
Super User Tom
Super User

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      .

 

yabwon
Onyx | Level 15

@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

_______________
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



sophielr
Fluorite | Level 6

Thank you, Tom. You show me a new method.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 529 views
  • 1 like
  • 3 in conversation