how to get total previous 5 days dose by subject date ?

[kk26]

Hi All,

I am trying to get sum of previous 5 days dose where flag="Y". I have multiple subjects in my data! 

Subject	Date	Dose	Flag	New Dose	Lstdose
101	5/13/2017	30
101	5/14/2017	30	Y	60	5/13/2017
101	5/15/2017	30
101	5/16/2017	30	Y	120	5/13/2017
101	5/17/2017	30
101	5/18/2017	45	Y	165	5/14/2017
101	5/19/2017	45
101	5/21/2017	45
101	5/25/2017	45	Y	90	5/21/2017

[novinosrin · Accepted solution]

data have;
infile cards truncover;
input Subject Date :mmddyy10. Dose 	Flag $;*	New Dose	Lstdose;
format date mmddyy10.;
cards;
101	5/13/2017	30	 	 	 
101	5/14/2017	30	Y	60	5/13/2017
101	5/15/2017	30	 	 	 
101	5/16/2017	30	Y	120	5/13/2017
101	5/17/2017	30	 	 	 
101	5/18/2017	45	Y	165	5/14/2017
101	5/19/2017	45	 	 	 
101	5/21/2017	45	 	 	 
101	5/25/2017	45	Y	90	5/21/2017
;

data want;
 if _n_=1 then do;
  dcl hash H (ordered:'y') ;
  h.definekey  ("_date") ;
  h.definedata ("_date","_dose") ;
  h.definedone () ;
  dcl hiter hi('h');
 end;
 do _n_=h.clear() by 0 until(last.subject);
  set have;
  by subject;
  _date=date;
  _dose=dose;
  h.add();
  if flag='Y' then do;
   do while(hi.next()=0);
    if intnx('day',date,-4)<=_date<=date then do;
     new_dose=sum(new_dose,_dose);
     Lstdose=min(Lstdose,_date);
    end;
   end;
   output;
   call missing(new_dose,lstdose);
  end;
  else output;
 end;
 format lstdose mmddyy10.;
 drop _:;
run;

proc print noobs;run;

Subject	Date	Dose	Flag	new_dose	Lstdose
101	05/13/2017	30		.	.
101	05/14/2017	30	Y	60	05/13/2017
101	05/15/2017	30		.	.
101	05/16/2017	30	Y	120	05/13/2017
101	05/17/2017	30		.	.
101	05/18/2017	45	Y	165	05/14/2017
101	05/19/2017	45		.	.
101	05/21/2017	45		.	.
101	05/25/2017	45	Y	90	05/21/2017

[kk26]

Hi @novinosrin ,

I am getting perfect result but do not understand code below , i have never seen such  programming. would you please explain why do we need to use this code below and what it does ? 

  dcl hash H (ordered:'y') ;
  h.definekey  ("_date") ;
  h.definedata ("_date","_dose") ;
  h.definedone () ;
  dcl hiter hi('h');

[novinosrin]

Hi @kk26  About to catch the bus to get home. I will respond to you with some details perhaps if I could login from home tonight or soon as I could tomorrow. [Eastern time]

[novinosrin]

Hi @kk26 ,Basically the solution utilizes a programming construct called Hash data structure. A lot of times this structure is considered a LOOK UP memory table however for some folks like me who are Hash enthusiasts, we tend to explore the possibilities beyond what's in the documentation.  Of course it requires enthusiasm and interest and a lot of reading to get to a level of comfort where you are at a place or point you begin to feel as the requirement narrates to your mind, the code is in the fingers.

The logic that I am using here is:

1. Use a Hash table to populate the records that satisfies the condition flag='Y' and the dates that fall within the 5 day intervals

2. Roll up the contents of the hash table and summarize.

3. Clear/Free up the memory once processing for a By Group is complete. 

That's as simple as that.

I follow the footsteps of Guru @hashman who is the coauthor of the book "Data Management Solutions Using SAS^® Hash Table Operations: A Business Intelligence Case Study"

along with Guru @DonH. I owe much of learning/gratitude to Paul D for offering his priceless knowledge both offline/online. I am lucky eh?

I highly encourage you to read that book if you are interested in elevating your sas programming skills. SAS press may offer some discounts. Please check with @Sian 

[kk26]

Thank you @novinosrin !

[hashman]

Hi @novinosrin: Yup, that's the logic, to a tee.

Thanks for another plug! 

Kind regards

Paul D. 

[DonH]

Thanks @novinosrin.

Based on a quick look, it appears you have covered all the bases.

I would like to add that @hashman and I were scheduled to present a Sunday morning tutorial at SGF2020. That obviously never happened.

But the good news is that our tutorial was selected to be one of the virtual presentations that will be made available to everyone. @hashman and I are working on our recordings of the bits and pieces which the SAS folks will stitch together. It is scheduled to be released as part of what is being referred to as Season 3.
Our presentation will be largely based on content from our book and we hope to cover a lot of ground.

[hashman]

Hi @novinosrin: do _n_ = h.clear() by 0, eh? I kind of recognize the style - David Cassell calls it a "dorfmanism" - but it's never occurred to Dorfman himself to use it in this manner. Kudos!

Kind regards

Paul D.  

[novinosrin]

Guru @hashman  Turn on your sense of humor. As much as I shamelessly admit that I copy you, I must also say you are getting old 🙂 hahaha. Let me be nice for a change- not old but more wise.I am sure you are aware I am not dumb enough to just read your posts/books/papers, however trust me I do take notes for future use. 

Btw, It's been a while. I sent you a courtesy touch base text message and didn't get any reply. I hope all's well with you and family? Things are beginning to get better at CT, and at work we are encountering a lot of Forbearance requests/planning to mitigate trouble debt/foreclosure across mortgage product lines. 

PS Pass on my regards to your family. My mother also wanted to send you & your family her regards. Take care and stay safe!

[DonH]

Anything that uses _n_ or _error_ or many of the other automatic variables for something other than their intended usage qualifies, by definition, as a Dorfmanism.

[hashman]

@DonH ... Thanks and fair enough ... but how do we qualify the variables that have no "intended usage" to begin with? For example, at times I use first._n_ and last._n_ as utility variables since the are perfectly fine numeric variables set to 0 at compile and auto-dropped from the output. Actually, first./last. can be attached in this manner to _error_, _iorc_ and any other variable. Even if it's not in the PDV - and even if it is a non-scalar variable in the PDV! Watch this:

data _null_ ;                                                                                                                           
  dcl hash  h ;                                                                                                                         
  dcl hiter i ;                                                                                                                         
  put first.h= last.h= first.i= last.i= first.var= last.var= ;                                                                                               
run ;

A curious thing is that though PUT shows all of them =0, you get the "unitialized" messages, too 😀. Looks as though the compiler sees no association of first.X / last.X with X at all unless X is part of BY - in which case, by the way, it sets them to 1. Proof:

data _null_ ;                                                                                                                           
  put first.name= last.name= ;                                                                                                          
  stop ;                                                                                                                                
  set sashelp.class ;                                                                                                                   
  by name ;                                                                                                                             
run ; 

Kind regards

Paul D.

[kk26]

hi @novinosrin 

is there any way to check if the dose has been changed at any time between lstdose date and current date where flag="Y" using same code ?
for example :
on 05/18/2017 dose is 45 and on 05/14/2017 the dose was 30. so dose has been changed from starting 30 to 45.

Thank you much in advance ! you are being a great help !

[novinosrin]

Hi @kk26  Do you mean something like this for your result to look like? I added a check_flag to the result

data want;
 if _n_=1 then do;
  dcl hash H (ordered:'y') ;
  h.definekey  ("_date") ;
  h.definedata ("_date","_dose") ;
  h.definedone () ;
  dcl hiter hi('h');
 end;
 do _n_=h.clear() by 0 until(last.subject);
  set have;
  by subject;
  _date=date;
  _dose=dose;
  h.add();
  if __dose>. and __dose ne dose then Check_flag=1;
  if flag='Y' then do;
   do while(hi.next()=0);
    if intnx('day',date,-4)<=_date<=date then do;
     new_dose=sum(new_dose,_dose);
     Lstdose=min(Lstdose,_date);
    end;
   end;
   output;
   Check_flag=.;
   __dose=dose;
   call missing(new_dose,lstdose);
  end;
  else output;
 end;
 format lstdose mmddyy10.;
 drop _:;
run;

[novinosrin]

Proc SQL readymeals solution:

data have;
infile cards truncover;
input Subject Date :mmddyy10. Dose 	Flag $;*	New Dose	Lstdose;
format date mmddyy10.;
cards;
101	5/13/2017	30	 	 	 
101	5/14/2017	30	Y	60	5/13/2017
101	5/15/2017	30	 	 	 
101	5/16/2017	30	Y	120	5/13/2017
101	5/17/2017	30	 	 	 
101	5/18/2017	45	Y	165	5/14/2017
101	5/19/2017	45	 	 	 
101	5/21/2017	45	 	 	 
101	5/25/2017	45	Y	90	5/21/2017
;
proc sql;
create table want as
select a.*,sum(b.dose) as new_dose,min(b.date) as Lstdose format=mmddyy10.
from have a left join have b
on a.subject=b.subject and intnx('day',a.date,-4)<=b.date<=a.date and a.flag='Y'
group by a.subject, a.date,a.dose,a.flag;
quit;
proc print noobs;run;

Subject	Date	Dose	Flag	new_dose	Lstdose
101	05/13/2017	30		.	.
101	05/14/2017	30	Y	60	05/13/2017
101	05/15/2017	30		.	.
101	05/16/2017	30	Y	120	05/13/2017
101	05/17/2017	30		.	.
101	05/18/2017	45	Y	165	05/14/2017
101	05/19/2017	45		.	.
101	05/21/2017	45		.	.
101	05/25/2017	45	Y	90	05/21/2017