DATA Step, Macro, Functions and more

Date sum reached Threshold

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

Date sum reached Threshold

Hi,

 

I have following data.  need to identify the  date when sum of daily med reached 200 or above.

 

IDFill_DTDays_SupplyEND_DTMED
11/12/2017302/10/201780
11/15/2017302/13/2017120
21/1/2017151/15/2017100
21/13/2017202/1/201780
31/1/2017301/30/201760
31/15/2017302/13/201760
31/25/2017302/23/2017100

 

i need data as follows

 

IDDate_reached_200MED  
11/15/2017200  
31/25/2017220  

 

Thanks


Accepted Solutions
Solution
‎01-19-2018 08:16 PM
Super User
Posts: 6,933

Re: Date sum reached Threshold

[ Edited ]
Posted in reply to sasuser77

You might need to make sure you output just one observation per ID.  Along those lines:

 

data want;

set have;

by ID;

if first.id then do;

   tot_med=0;

   flag=0;

end;

tot_med + med;

if tot_med >= 200 then flag + 1;

if flag=1 then output;

keep id fill_dt tot_med;

rename fill_dt = Date_reached_200;

run;

 

That gives you the first date when the ID hit 200 total.

View solution in original post


All Replies
Super User
Posts: 2,061

Re: Date sum reached Threshold

Posted in reply to sasuser77
data have;
input ID	Fill_DT : mmddyy10.	Days_Supply	END_DT : mmddyy10.	MED;
datalines;
1	1/12/2017	30	2/10/2017	80
1	1/15/2017	30	2/13/2017	120
2	1/1/2017	15	1/15/2017	100
2	1/13/2017	20	2/1/2017	80
3	1/1/2017	30	1/30/2017	60
3	1/15/2017	30	2/13/2017	60
3	1/25/2017	30	2/23/2017	100
;

data want;
set have;
by id;
if first.id then sum=0;
sum+MED;
if sum>=200 then output;
format Fill_DT END_DT mmddyy10.;
run;
Solution
‎01-19-2018 08:16 PM
Super User
Posts: 6,933

Re: Date sum reached Threshold

[ Edited ]
Posted in reply to sasuser77

You might need to make sure you output just one observation per ID.  Along those lines:

 

data want;

set have;

by ID;

if first.id then do;

   tot_med=0;

   flag=0;

end;

tot_med + med;

if tot_med >= 200 then flag + 1;

if flag=1 then output;

keep id fill_dt tot_med;

rename fill_dt = Date_reached_200;

run;

 

That gives you the first date when the ID hit 200 total.

Contributor
Posts: 25

Re: Date sum reached Threshold

Posted in reply to Astounding
both of the above answers yield empty data set.

I may not be clear with my questions.
1 1/12/2017 30 2/10/2017 80
MED represents for each day it will be 80 starting from Fill_Dt to End_DT. At any point of time it reaches above 200 for that ID, I need to output that id along with date.
Super User
Posts: 2,061

Re: Date sum reached Threshold

Posted in reply to sasuser77

We can only test with the sample you give us. 

 

 Here is my log and output


5361 data _null_;
5362 if _n_=1 then do;
5363 if 0 then set have;
5364 declare hash h(dataset:'have(obs=0)',multidata:'y',ordered:'y');
5365 rc = h.defineKey('id');
5366 rc = h.defineData(all:'y');
5367 rc = h.defineDone();
5368 end;
5369 sum=0;
5370 do until(last.id);
5371 set have;
5372 by id;
5373 sum+med;
5374 if h.check() ne 0 and sum>=200 then h.add();
5375 end;
5376 h.output(dataset:'want');
5377 format Fill_DT END_DT mmddyy10.;
5378 run;

NOTE: There were 0 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT has 1 observations and 5 variables.
NOTE: The data set WORK.WANT has 1 observations and 5 variables.
NOTE: The data set WORK.WANT has 2 observations and 5 variables.
NOTE: There were 7 observations read from the data set WORK.HAVE.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds

 

IDFill_DTDays_SupplyEND_DTMED
11/15/2017302/13/2017120
31/25/2017302/23/2017100

 

Contributor
Posts: 25

Re: Date sum reached Threshold

Posted in reply to novinosrin
looks like copy paste error.
Super User
Posts: 6,933

Re: Date sum reached Threshold

Posted in reply to sasuser77

If I understand the question, the programming would be fairly similar:

 

data want;

set have;

by ID;

if first.id then do;

   tot_med=0;

   flag=0;

end;

if flag=0;

start_dt = end_dt - days_supply + 1;

do Date_reached_200 = start_dt to End_dt;

   tot_med + med;

   if tot_med >= 200 then flag + 1;

   if flag=1 then output;

end;

keep id Date_reached_200 tot_med;

run;

 

The reason for computing Start_Dt is that the date ranges might overlap.  In your sample data, for example, you can't use the fill date as the starting point.  Usually a person refills the prescription a few days before running out.  If you are not careful, you will count the person as taking both the original plus the refill on the same date.  Here, I'm assuming the patient uses up the first prescription before starting on the second prescription.

Contributor
Posts: 25

Re: Date sum reached Threshold

Posted in reply to Astounding

The end_date shown here is also a computed from fill_dt. In general i only have fill_dt. So,l am always assuming start as fill_date and end_date as fill_dt+days_supply-1.

Super User
Posts: 6,933

Re: Date sum reached Threshold

Posted in reply to Astounding

Revised one last time.  You really need to explain what is in your data instead of adding bits and pieces along the way.

 

data want;

set have;

by ID;

if first.id then do;

   tot_med=0;

   flag=0;

   Date_reached_200 = Fill_dt - 1;

end;

if flag=0;

do day = 1 to days_supply;

   tot_med + med;

   Date_reached_200 + 1;

   if tot_med >= 200 then flag + 1;

   if flag=1 then do;

      output;

      delete;

   end;

end;

keep id Date_reached_200 tot_med;

run;

Contributor
Posts: 25

Re: Date sum reached Threshold

Posted in reply to Astounding

sorry for bits and pieces.

 

i am trying to look if at any point of time tot_MED for that day has reached above 200 or not with in reporting period (7/1/17 - 12/31/17). if the fill date is before reporting period and days supply extending into reporting period i am considering bg_date as reporting period begin date instead of actual fill_Dt and same for that extending out side reporting period.

 

IDDays_supplyMEDFill_DTend_Datebg_Date
13022.56/30/20177/29/20177/1/2017
1301806/30/20177/29/20177/1/2017
13022.57/31/20178/29/20177/31/2017
13022.59/1/20179/30/20179/1/2017
13022.59/29/201710/28/20179/29/2017
1301809/29/201710/28/20179/29/2017
23037.56/30/20177/29/20177/1/2017
23037.57/31/20178/29/20177/31/2017
23037.58/31/20179/29/20178/31/2017
23037.510/2/201710/31/201710/2/2017
23037.510/31/201711/29/201710/31/2017
23037.511/29/201712/28/201711/29/2017
23037.512/27/201712/31/201712/27/2017
33187/18/20177/20/20177/18/2017
32508/12/20178/13/20178/12/2017
451510/4/201710/8/201710/4/2017
5907.56/21/20179/18/20177/1/2017
590159/22/201712/20/20179/22/2017
630606/13/20177/12/20177/1/2017
630607/14/20178/12/20177/14/2017
630608/10/20179/8/20178/10/2017
630609/11/201710/10/20179/11/2017
7301606/6/20177/5/20177/1/2017
7301607/6/20178/4/20177/6/2017
7301608/4/20179/2/20178/4/2017
730459/2/201710/1/20179/2/2017
7304510/1/201710/30/201710/1/2017
7304510/30/201711/28/201710/30/2017
7304511/28/201712/27/201711/28/2017
Super User
Posts: 2,061

Re: Date sum reached Threshold

[ Edited ]
Posted in reply to sasuser77
data _null_;
if _n_=1 then do;
if 0 then set have;
   declare hash h(dataset:'have(obs=0)',multidata:'y',ordered:'y');
   rc = h.defineKey('id');
   rc = h.defineData(all:'y');
   rc = h.defineDone();
end;
sum=0;
do until(last.id);
set have;
by id;
sum+med;
if h.check() ne 0 and sum>=200  then h.add();
end;
h.output(dataset:'want');
format  Fill_DT END_DT mmddyy10.;
run;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 371 views
  • 1 like
  • 3 in conversation