Fill in missing minutes to a dataset

[bsam17]

Hi

I have a dataset with two years of data, broken down by minute. Most minutes are accounted for, but some minutes have no data and are missing. I want to fill in these missing minutes and assign them a value of 0. Is there a simple way to do this, perhaps using proc expand?

For example, if I have

Minute	Valule
12:14	1
12:15	7
12:18	9
12:19	4

I would like to expand this to:

Minute	Value
12:14	1
12:15	7
12:16	0
12:17	0
12:18	9
12:19	4

Thanks for any suggestions.

[Steelers_In_DC · Accepted solution]

DATA have;

informat minute time.;

format minute time.;

infile cards dsd;

input Minute value;

cards;

12:14,1

12:15,7

12:18,9

12:19,4

;

run;

proc expand data=have out=want from=minute to=minute observed=end method=none;

    id minute;

run;

[sbb]

With no SAS/ETS, a DATA step approach works where DO/END with OUTPUT loop creates a "seed" file with the VALUE retained as 0.

Then merge (with a suitable BY statement) the _FILL seed file onto the source data to create a merged copy - the MERGE statement file order matters here.

Scott Barry

SBBWorks, Inc.

[bsam17]

Thanks, Mark! This seems to work almost perfectly, but I have two questions:

1) I get warnings in the log whenever it fills in a minute. How do I supress this warning?

2) How would I carry down the last value instead of filling them in as missings?

[Steelers_In_DC]

You want to use a by statement to fill in the blanks:

proc expand data=have out=want from=minute to=minute observed=end method=none;

     by id;

    id minute;

run;

You can write out the log to anywhere you want but I never recommend doing this.  It would go like this:

proc printto log="/sas/user_data/local/filename.log"; run;

proc expand data=have out=want from=minute to=minute observed=end method=none;

     by id;

    id minute;

run;

proc printto;run;

Anytime I've used this the log was unusable, I would advise you to find out why you are getting the warnings and try to make an adjustment.  If the warnings make the other part of the log unusable then I might do this but I only use it as a last resort.

[Ksharp]

Code: Program

DATA have;
informat minute time.;
format minute time.;
infile cards dsd;
input Minute value;
cards;
12:14,1
12:15,7
12:18,9
12:19,4
;
run;

data want;
 merge have have(firstobs=2 keep=minute rename=(minute =_m));
 output;
 do i=minute+60 to ifn(missing(_m),0,_m-60) by 60;
  minute=i;
  value=0;
  output;
 end;
drop i _m;
run;