I have a data set with respect to dates as below.
I would like to identify and insert the missing date series in each group and rest of the variables value should be previous record data.
Data have:
Date Employee var2 var3 var4 ..... ...
01aug2021 Tom
02aug2021 Tom
04aug2021 Tom
05aug2021 Tom
06aug2021 Tom
01Sep2021 John
03sep2021 John
01Sep2021 John
As you can see 03aug2021 is missing in under TOM and 02Sep2021 is missing from John group.
Data Want:
Date Employee var2 var3 var4 ..... ...
01aug2021 Tom
02aug2021 Tom
03aug2021 Tom
04aug2021 Tom
05aug2021 Tom
06aug2021 Tom
01Sep2021 John
02Sep2021 John
03sep2021 John
01Sep2021 John
data have; input Date : date9. Employee $; format date date9.; cards; 01aug2021 Tom 02aug2021 Tom 04aug2021 Tom 05aug2021 Tom 06aug2021 Tom 01Sep2021 John 03sep2021 John 04Sep2021 John ; data want; merge have have(firstobs=2 keep=date employee rename=(date=_date employee=_employee)); output; if employee=_employee then do; do date=date+1 to _date-1; output; end; end; drop _:; run;
Hello,
I have only a minute but, if not solved by tomorrow, I can elaborate on this topic.
For the missing dates, my colleague Gerhard Svolba has published numerous community library articles on this topic, using the SAS/ETS procedures PROC TIMESERIES or PROC EXPAND or PROC TIMEDATA and maybe also PROC TIMEID (??). See the Communities Library.
The missing value imputation method you are looking after is called "Last Value Carry Forward" (LVCF) or "forward-filling". Search these communities with those keywords and you will find numerous hits (numerous topics on this subject).
Good luck,
Koen
On top of my previous reply (see above) ...
Replace MISSING VALUES in TIMESERIES DATA using PROC EXPAND and PROC TIMESERIES
Posted 02-04-2021 10:45 AM | by gsvolba
https://communities.sas.com/t5/SAS-Communities-Library/Replace-MISSING-VALUES-in-TIMESERIES-DATA-usi...
Using the TIMESERIES procedure to check the continuity of your timeseries data
Posted 01-28-2021 03:40 PM | by gsvolba
https://communities.sas.com/t5/SAS-Communities-Library/Using-the-TIMESERIES-procedure-to-check-the-c...
Koen
You basically want to carry forward a given record over any internal "holes" in the series:
data want (drop=nxt_:);
set have (keep=employee);
by employee;
merge have have (firstobs=2 keep=date rename=(date=nxt_date));
output;
if last.employee=0 then do while (date<nxt_date-1);
date=date+1;
output;
end;
run;
The first SET statement is there just to generate the first.employee and last.employee dummies for testing in an IF statement.
data have; input Date : date9. Employee $; format date date9.; cards; 01aug2021 Tom 02aug2021 Tom 04aug2021 Tom 05aug2021 Tom 06aug2021 Tom 01Sep2021 John 03sep2021 John 04Sep2021 John ; data want; merge have have(firstobs=2 keep=date employee rename=(date=_date employee=_employee)); output; if employee=_employee then do; do date=date+1 to _date-1; output; end; end; drop _:; 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.