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
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;
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.
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..
@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?
@mkt_apprentice Your sample suggests they are "continuous", so the _date to date gymnastics will take care of that. 🙂
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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.