DATA Step, Macro, Functions and more

Creating new observations

Reply
Occasional Contributor
Posts: 13

Creating new observations

[ Edited ]

(SAS 9.4) I am working with a fairly large data set and have data on numerous animals. I have about 100 days of data for each animal and want to create a data set that has observations for each day, regardless of if i actually have data on those days (all of my other variables would be missing). For example, I want an observation for each day (day 0 through day 100) for each animal in my data set (each day for each animal is different). I know I could create a day for each animal in a separate file and merge the two, but this will be rather timely. Is there a way I can take my existing data sheet and have SAS look through the dates (I also have a variable that courresponds to the date that is just the day, such as day 1, day 2, etc) and add in observations when the data skips a day?

 

Thanks

 

 

Super User
Posts: 19,877

Re: Creating new observations

Occasional Contributor
Posts: 13

Re: Creating new observations

Okay, thanks for your help.

Respected Advisor
Posts: 4,936

Re: Creating new observations

"I could create a day for each animal in a separate file and merge the two"

 

Unless you have millions of animals, or you are doing this for free, this is the most efficient way to go about it. 

PG
Super User
Posts: 5,518

Re: Creating new observations

It's clumsy, but it can be done.  Here's an approach:

 

proc sort data=have;

by animal day;

run;

 

data want;

set have;

by animal day;

array chars {*} _character_;

array nums {*} _numeric_;

output;

prior_day = lag(day);

copy_of_animal = animal;

copy_of_day = day;

call missing (of chars{*});

call missing (of nums{*});

animal = copy_of_animal;

if first.animal and copy_of_day > 1 then do day=1 to copy_of_day-1;

   output;

end;

else if copy_of_day > prior_day + 1 then do day=prior_day+1 to copy_of_day-1;

   output;

end;

if last.animal and copy_of_day < 100 then do day = copy_of_day + 1 to 100;

   output;

end;

drop copy_of_day copy_of_animal prior_day;

run;

 

You will need to re-sort the observations afterwards.

 

You said you had 100 days of data per animal, but also said that DAY goes from 0 to 100.  Those two are actually slightly different, so I assumed that DAY should go from 1 to 100.  Minimal change would be required to go from 0 to 100.

 

The code is untested, but likely to work as is.  It does assume that DAY is numeric.  Good luck.

Ask a Question
Discussion stats
  • 4 replies
  • 282 views
  • 0 likes
  • 4 in conversation