BookmarkSubscribeRSS Feed
mkt_apprentice
Obsidian | Level 7

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

 

7 REPLIES 7
novinosrin
Tourmaline | Level 20

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
Obsidian | Level 7

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
Super User
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
Obsidian | Level 7

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
Super User

@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
Tourmaline | Level 20

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

Ksharp
Super User
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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 7 replies
  • 822 views
  • 1 like
  • 4 in conversation