DATA Step, Macro, Functions and more

Fill in missing minutes to a dataset

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Fill in missing minutes to a dataset

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

MinuteValule
12:141
12:157
12:189
12:194

I would like to expand this to:

MinuteValue
12:141
12:157
12:160
12:170
12:189
12:194

Thanks for any suggestions.


Accepted Solutions
Solution
‎06-29-2015 02:34 PM
Valued Guide
Posts: 860

Re: Fill in missing minutes to a dataset

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;

View solution in original post


All Replies
Solution
‎06-29-2015 02:34 PM
Valued Guide
Posts: 860

Re: Fill in missing minutes to a dataset

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;

Super Contributor
Super Contributor
Posts: 3,174

Re: Fill in missing minutes to a dataset

Posted in reply to Steelers_In_DC

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.

Occasional Contributor
Posts: 8

Re: Fill in missing minutes to a dataset

Posted in reply to Steelers_In_DC

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?

Valued Guide
Posts: 860

Re: Fill in missing minutes to a dataset

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.

Super User
Posts: 10,028

Re: Fill in missing minutes to a dataset

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;
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 282 views
  • 0 likes
  • 4 in conversation