topic Re: compute the diff between dates vertically in SAS Programming

compute the diff between dates vertically

mona4u — Wed, 02 May 2018 18:58:21 GMT

MemberID	ServiceDate
1	1/17/2017
1	1/2/2017
10	6/27/2017
11	7/31/2017
12	11/1/2017
13	6/14/2017
13	2/4/2016
14	5/30/2017
15	8/11/2017
15	12/11/2016
16	10/16/2017
16	1/14/2017
16	2/17/2016
16	11/25/2015
17	7/8/2017
18	9/14/2017
18	3/3/2017
19	10/12/2017
2	2/14/2017
2	5/25/2016
20	12/19/2017
21	8/16/2017
21	5/25/2015
21	3/11/2014
3	3/17/2017
30	2/18/2018
30	4/16/2016
31	1/21/2018
32	3/6/2018
33	2/7/2018
34	1/3/2018
34	2/15/2016
35	1/13/2018
36	3/30/2018
37	3/16/2018
38	5/1/2016
39	6/15/2016
4	3/21/2017
4	10/16/2016
4	4/12/2013
40	11/27/2016
44	10/31/2017
45	4/18/2017
45	12/11/2015
46	5/25/2016
47	12/23/2015
48	6/12/2017
49	7/14/2017
5	1/11/2017
5	7/4/2016
50	10/12/2017
50	5/25/2017
50	1/11/2016
6	2/8/2017
7	5/26/2017
7	1/15/2017
7	6/25/2016
8	4/16/2017
9	9/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

MemberID	ServiceDate	Year
1	1/17/2017	Measurement Year
1	1/2/2017	Measurement Year
10	6/27/2017	Measurement Year
11	7/31/2017	Measurement Year
12	11/1/2017	Measurement Year
13	6/14/2017	Prior Year
13	2/4/2016	Measurement Year
14	5/30/2017	Measurement Year
15	8/11/2017	Measurement Year
15	12/11/2016	Measurement Year
16	10/16/2017	Measurement Year
16	1/14/2017	Measurement Year
16	2/17/2016	Prior Year
16	11/25/2015	Prior Year

Re: compute the diff between dates vertically

ballardw — Wed, 02 May 2018 19:22:13 GMT

@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?

Re: compute the diff between dates vertically

art297 — Wed, 02 May 2018 19:26:41 GMT

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

Re: compute the diff between dates vertically

mona4u — Wed, 02 May 2018 19:33:07 GMT

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

Re: compute the diff between dates vertically

mona4u — Wed, 02 May 2018 19:37:13 GMT

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

Re: compute the diff between dates vertically

art297 — Wed, 02 May 2018 20:25:03 GMT

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