Create new observations with changing time information

[mkt_apprentice]

Hi SAS communities,

When I create new observations, some variables stay the same and some variables are changing (time or date). How can create new observations with these changing data? 

My data is like this:

id  day  date                 drink       age

1    1     20180907          1          24

1    2            .                  0          24

1    3     20180909          3          24

2    1     20171211          2          30

2    2            .                  0          30

2    3            .                  0          30

How can I add the data into the missing data that I can infer from other cells? Data would look like this?

id  day  date                 drink       age

1    1     20180907          1          24

1    2     20180908          0          24

1    3     20180909          3          24

2    1     20171211          2          30

2    2     20171212          0          30

2    3     20171213          0          30

[novinosrin]

Hi @mkt_apprentice   If the startdate for each ID i.e the first obs of each id is never missing like in your sample, then it's no big deal, rather a mere counter

data have;
input id  day  date    :yymmdd10.             drink       age;
format date yymmdd10.;
cards;
1    1     20180907          1          24
1    2            .                  0          24
1    3     20180909          3          24
2    1     20171211          2          30
2    2            .                  0          30
2    3            .                  0          30
;

data want;
set have;
by id;
retain _Date;
if first.id then _date=date;
else _date+1;
date=_date;
drop _date;
run;

[mkt_apprentice]

Hi @novinosrin , what if the missing values are in the 2nd, 3rd... date? They are in different day (1 to 5) rather than only after the 1st date. 

[Reeza]

If it's continuous it's fine, can you show some more data examples that illustrate your issue if the code doesn't work? Please make your example data as reflective of the problem as possible.

[mkt_apprentice]

The data is like the one I mentioned in the first post.. I am trying to infer the date based on the data available. day is in consecutive order.. 

[Reeza]

---

@mkt_apprentice wrote:

The data is like the one I mentioned in the first post.. I am trying to infer the date based on the data available. day is in consecutive order.. 

---

Then did you run the proposed solution? Did it not handle that case?

[novinosrin]

@mkt_apprentice  Your sample suggests they are "continuous", so the _date to date gymnastics will take care of that. 🙂

[Ksharp]

data have;
input id  day  date    :yymmdd10.             drink       age;
format date yymmdd10.;
cards;
1    1     20180907          1          24
1    2            .                  0          24
1    3     20180909          3          24
2    1     20171211          2          30
2    2            .                  0          30
2    3            .                  0          30
;
data temp;
 set have(where=(date is not missing));
 by id;
 if first.id;
 keep id day date;
run;
data want;
 merge have temp(rename=(day=_day date=_date));
 by id ;
 if missing(date) then date=_date-(_day-day);
 drop _:;
run;