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;
Firstly, you already have your dummy variable coded as y/n on whether the metric values change from one row to the next?
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;
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.
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;
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.