topic Re: Creating new observations in SAS Programming

Creating new observations

llt34c — Wed, 25 May 2016 17:24:13 GMT

(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

Re: Creating new observations

Reeza — Wed, 25 May 2016 17:48:00 GMT

You can use PROC EXPAND but this requires SAS/ETS license.

http://support.sas.com/documentation/cdl/en/etsug/60372/HTML/default/viewer.htm#etsug_tsdata_sect045.htm

Re: Creating new observations

llt34c — Wed, 25 May 2016 17:58:18 GMT

Okay, thanks for your help.

Re: Creating new observations

PGStats — Wed, 25 May 2016 18:04:24 GMT

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

Re: Creating new observations

Astounding — Wed, 25 May 2016 18:38:12 GMT

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.