Input row at each instance of change OR at 12 months

[Jane7476]

Hey,

I want to add a dummy date variable:

- if the metric column value changes

- every 12 months after the first recorded date for the by group (id) even if the metric column value doesn't change

Can someone help produce the WANT dataset? Thanks.

P.S.: Even if metric doesn't change I need a date populated every 12 months after the first known date of each id.

data have;

informat id 1. date_record mmddyy10. metric 1. metric_change $1.;

input id date_record metric metric_change;

format date_record date9.;

datalines;

1 01/01/2010 5 n

1 01/01/2011 5 n

1 01/01/2014 6 y

1 06/01/2014 6 n

1 11/30/2014 6 n

1 05/01/2015 8 y

1 12/01/2015 3 y

2 03/01/2015 3 n

2 03/05/2015 4 y

2 02/28/2016 5 y

;

run;

data want;

informat id 1. date_record mmddyy10. metric 1. date_wanted mmddyy10.;

input id date_record metric date_wanted;

format date_record date9.;

format date_wanted date9.;

datalines;

1 01/01/2010 5 01/01/2010

1 01/01/2011 5 01/01/2011

1 5 01/01/2012

1 5 01/01/2013

1 01/01/2014 6 01/01/2014

1 6 01/01/2015

1 05/01/2015 8 05/01/2015

1 12/01/2015 3 12/01/2015

2 03/01/2015 3 03/01/2015

2 03/05/2015 4 03/05/2015

2 4 03/05/2016

2 02/28/2016 5 02/28/2016

;

run;

[PeterClemmensen]

Firstly, you already have your dummy variable coded as y/n on whether the metric values change from one row to the next?

[RW9]

I couldn't follow your logic versus required output very well, why does row two have a date as that isn't a change, nor is it a year after, then next row however is:

data have;
  informat id 1. date_record mmddyy10. metric 1. metric_change $1.;
  input id date_record metric metric_change;
  format date_record date9.;
datalines;
1 01/01/2010 5 n
1 01/01/2011 5 n
1 01/01/2014 6 y
1 06/01/2014 6 n
1 11/30/2014 6 n
1 05/01/2015 8 y
1 12/01/2015 3 y
2 03/01/2015 3 n
2 03/05/2015 4 y
2 02/28/2016 5 y
;
run;

data want (drop=dt);
  set have;
  by id;
  retain dt;
  if first.id then do;
    date_wanted=date_record;
    dt=date_record;
  end;
  if metric_change="y" then date_wanted=date_record;
  if date_record > intnx('month',dt,12) then do;
    date_wanted=date_record;
    dt=date_record;
  end;
  format date_wanted date9.;
run;

[Jane7476]

Thanks RW9.

The first instance of change in METRIC (from 5 to 6) for id 1 is on 01/01/2014. Before this it has two dates - 01/01/2010 and 01/01/2011.

In this case the the records i need for id 1 are:

01/01/2010  ----this is the first instance so needed

01/01/2011 -----this is the instance after a year where data is available but no change in metric observed

01/01/2012 -----new row of data need as date is one year after previous row (even if no change in metric)

01/01/2013 -----new row of data need as date is one year after previous row (even if no change in metric)

01/01/2014------this is a change in metric record so need it

01/01/2015------new row of data need as date is one year after previous row (even if no change in metric)

05/01/2015------this is a change in metric record so need it 

12/01/2015------this is a change in metric record so need it 

Hope this clarifies things. I need date at every 12 months irrespective of change in metric. Thanks.

[RW9]

Ah, ok I had it only for over 1 year, not the same as:

data have;
  informat id 1. date_record mmddyy10. metric 1. metric_change $1.;
  input id date_record metric metric_change;
  format date_record date9.;
datalines;
1 01/01/2010 5 n
1 01/01/2011 5 n
1 01/01/2014 6 y
1 06/01/2014 6 n
1 11/30/2014 6 n
1 05/01/2015 8 y
1 12/01/2015 3 y
2 03/01/2015 3 n
2 03/05/2015 4 y
2 02/28/2016 5 y
;
run;

data want (drop=dt);
  set have;
  by id;
  retain dt;
  if first.id then do;
    date_wanted=date_record;
    dt=date_record;
  end;
  if metric_change="y" then date_wanted=date_record;
  if date_record >= intnx('month',dt,12) then do;
    date_wanted=date_record;
    dt=date_record;
  end;
  format date_wanted date9.;
run;

[Jane7476]

What changes will I have to make to

if date_record >= intnx('month',dt,12) then do;

date_wanted=date_record;

dt=date_record;

to ensure that I am putting in dummy rows with dates at yearly intervals even if there is no change in metric?

Thanks