BookmarkSubscribeRSS Feed
Jane7476
Calcite | Level 5

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;

5 REPLIES 5
PeterClemmensen
Tourmaline | Level 20

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 850 views
  • 0 likes
  • 3 in conversation