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 |
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 |
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 |
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');
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]
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
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.
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.
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!
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.
@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.
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;
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 |
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.