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.
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;
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;
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.
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?
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.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.