Hello,
I have a dataset and need to calculate the applications through out the month they where touched and not touched and thus need to add dates on missing days. My dataset looks like below.
Actual dataset.
App# Modified Time
001 09/07/16 11:03:08
001 09/08/16 12:26:15
001 09/08/16 11:04:52
001 09/12/16 09:05:10
002 09/01/16 11:03:08
002 09/02/16 12:26:15
002 09/04/16 11:04:52
002 09/09/16 09:05:10
Correct output should look like below
App# Modified Time
001 09/07/16 11:03:08
001 09/08/16 12:26:15
001 09/08/16 11:04:52
001 09/09/16 01:01:01
001 09/10/16 01:01:01
001 09/11/16 01:01:01
001 09/12/16 09:05:10
002 09/01/16 11:03:08
002 09/02/16 12:26:15
002 09/03/16 01:01:01
002 09/04/16 11:04:52
002 09/05/16 01:01:01
002 09/06/16 01:01:01
002 09/07/16 01:01:01
002 09/08/16 01:01:01
002 09/09/16 11:04:52
Thank you.
try code:
set have;
by app;
save_date = date; prev_date=lag(date); diff = save_date - prev_date;
DROP save_date prev_date diff;
if first.app then output;
else do;
if diff ge 2 then do;
do date = prev_date +1 to save_date by 1;
/* time = ...whatever you preffer ... */
output;
end;
end; else output;
end;
run;
They are two different variables. For missing dates, time can be anything.
Assuming first date and last date per application exists, and that date is a SAS date,
then you can do:
data want;
set have;
by app;
if not first.app and not last.app then do;
save_date = date; drop save_date;
if save_date < lag(date) +1 then do;
do date = lag(date)+1 to save_date - 1;
/* time = ...whatever you prefer ... */
output;
end;
end;
run;
it does not work for me. It generates error for do loop. Thank you
try code:
set have;
by app;
save_date = date; prev_date=lag(date); diff = save_date - prev_date;
DROP save_date prev_date diff;
if first.app then output;
else do;
if diff ge 2 then do;
do date = prev_date +1 to save_date by 1;
/* time = ...whatever you preffer ... */
output;
end;
end; else output;
end;
run;
Thank you. This works.
data have; input App ModifiedTime anydtdtm32.; date=datepart(modifiedtime); format modifiedtime datetime. date date9.; cards; 001 09/07/16 11:03:08 001 09/08/16 12:26:15 001 09/08/16 11:04:52 001 09/12/16 09:05:10 002 09/01/16 11:03:08 002 09/02/16 12:26:15 002 09/04/16 11:04:52 002 09/09/16 09:05:10 ; run; proc sql; create table temp as select app,datepart(min(modifiedtime)) as min , datepart(max(modifiedtime)) as max from have group by app; quit; data x; set temp; do date=min to max; output; end; format date date9.; keep app date; run; data want; merge have x; by app date; new=coalesce(modifiedtime,dhms(date,1,1,1)); format new datetime.; drop date modifiedtime; run;
Yesterday I used your other code and solved it but it was way longer than what you have provided here.
Thank you. It works for me.
Yes. There are many ways in SAS can solve the same question. data have; input App ModifiedTime anydtdtm32.; date=datepart(modifiedtime); format modifiedtime datetime. date date9.; cards; 001 09/07/16 11:03:08 001 09/08/16 12:26:15 001 09/08/16 11:04:52 001 09/12/16 09:05:10 002 09/01/16 11:03:08 002 09/02/16 12:26:15 002 09/04/16 11:04:52 002 09/09/16 09:05:10 ; run; data want; merge have have(keep=app date rename=(app=_app date=_date) firstobs=2); output; if app=_app then do; do i=date+1 to _date-1; modifiedtime=dhms(i,1,1,1);output; end; end; drop _: i date; 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.