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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.