DATA Step, Macro, Functions and more

compute the diff between dates vertically

Reply
Regular Contributor
Posts: 166

compute the diff between dates vertically

MemberIDServiceDate
11/17/2017
11/2/2017
106/27/2017
117/31/2017
1211/1/2017
136/14/2017
132/4/2016
145/30/2017
158/11/2017
1512/11/2016
1610/16/2017
161/14/2017
162/17/2016
1611/25/2015
177/8/2017
189/14/2017
183/3/2017
1910/12/2017
22/14/2017
25/25/2016
2012/19/2017
218/16/2017
215/25/2015
213/11/2014
33/17/2017
302/18/2018
304/16/2016
311/21/2018
323/6/2018
332/7/2018
341/3/2018
342/15/2016
351/13/2018
363/30/2018
373/16/2018
385/1/2016
396/15/2016
43/21/2017
410/16/2016
44/12/2013
4011/27/2016
4410/31/2017
454/18/2017
4512/11/2015
465/25/2016
4712/23/2015
486/12/2017
497/14/2017
51/11/2017
57/4/2016
5010/12/2017
505/25/2017
501/11/2016
62/8/2017
75/26/2017
71/15/2017
76/25/2016
84/16/2017
99/21/2017

 

this is a sample from my data and I want to know what is the best way to assign 

new variable call year  it count this way 

for each member id if there is only one service date then year=Measurement Year

but if the member id has more than service date then 

Measurement Year- The 12 month period that is being reviewed by the current run of data.  This does not have to start on January 1st

Prior Year- The 12 month period immediately preceding the measurement year

what is the best way to assign measurement year or prior year 

 

this is a sample of the result that I aim for

MemberIDServiceDateYear  
11/17/2017Measurement Year 
11/2/2017Measurement Year 
106/27/2017Measurement Year 
117/31/2017Measurement Year 
1211/1/2017Measurement Year 
136/14/2017Prior Year 
132/4/2016Measurement Year 
145/30/2017Measurement Year 
158/11/2017Measurement Year 
1512/11/2016Measurement Year 
1610/16/2017Measurement Year 
161/14/2017Measurement Year 
162/17/2016Prior Year 
1611/25/2015Prior Year 
Super User
Posts: 13,950

Re: compute the diff between dates vertically


@mona4u wrote:

 

for each member id if there is only one service date then year=Measurement Year

but if the member id has more than service date then 

Measurement Year- The 12 month period that is being reviewed by the current run of data.  This does not have to start on January 1st

Prior Year- The 12 month period immediately preceding the measurement year

what is the best way to assign measurement year or prior year 

 


Define "current run of data". There is nothing in the example data that shows what that might mean. We would need some rule or value to set as the first and last dates for the "measurement Year". And what do you want if a date is actually 2 or more "measurement years" prior?

 

 

Regular Contributor
Posts: 166

Re: compute the diff between dates vertically

I also got confused by the same expression as well. but I guess that the current run of the data is the most current  service date for that particular obs 

Super User
Posts: 8,220

Re: compute the diff between dates vertically

You hadn't stated what defines the measurement year, so I had guess it was the most recent date for a given memberID, and continued for one year prior to that date.

 

If that was correct, then the following would account for your latest criterion:

proc sort data=have;
  by MemberID descending ServiceDate;
run;

data want (drop=year_start year_boundary);
  set have;
  by MemberID descending ServiceDate;
  retain year_start year_boundary;
  if first.MemberID then do;
    Year='Measurement Year';
    year_start=intnx('year',ServiceDate,-1,'S');
    year_boundary=intnx('year',ServiceDate,-2,'S');
  end;
  else if ServiceDate gt year_start then year='Measurement Year';
  else if ServiceDate gt year_boundary then year='Prior Year';
  else call missing(year);
run;

Art, CEO, AnalystFinder.com

 

Super User
Posts: 8,220

Re: compute the diff between dates vertically

I think you are asking how to do the following:

proc sort data=have;
  by MemberID descending ServiceDate;
run;

data want;
  set have;
  by MemberID descending ServiceDate;
  retain year_start;
  if first.MemberID then do;
    Year='Measurement Year';
    year_start=intnx('year',ServiceDate,-1,'S');
  end;
  else if ServiceDate lt year_start then year='Prior Year';
  else year='Measurement Year';
run;

Art, CEO, AnalystFinder.com

 

Regular Contributor
Posts: 166

Re: compute the diff between dates vertically

I liked the answer even though 

 

Prior Year- The 12 month period immediately preceding the measurement year

and sometimes I have some obs have more than 24 months and I don't need to assign anything to those particular cases    

 

Ask a Question
Discussion stats
  • 5 replies
  • 131 views
  • 2 likes
  • 3 in conversation