topic Create new observations with changing time information in SAS Programming

Create new observations with changing time information

mkt_apprentice — Wed, 27 Mar 2019 22:01:53 GMT

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

Re: Create new observations with changing time information

novinosrin — Wed, 27 Mar 2019 22:19:27 GMT

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;

Re: Create new observations with changing time information

mkt_apprentice — Wed, 27 Mar 2019 22:32:13 GMT

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.

Re: Create new observations with changing time information

Reeza — Wed, 27 Mar 2019 22:34:20 GMT

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.

Re: Create new observations with changing time information

novinosrin — Wed, 27 Mar 2019 22:37:03 GMT

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

Re: Create new observations with changing time information

mkt_apprentice — Wed, 27 Mar 2019 22:37:58 GMT

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..

Re: Create new observations with changing time information

Reeza — Wed, 27 Mar 2019 22:48:15 GMT

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

Re: Create new observations with changing time information

Ksharp — Thu, 28 Mar 2019 12:23:15 GMT

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;