Hi ,
i have a issue in a dataset where i need to insert a duplicate rows on the months where the clm_key is missing betwwen the first.clm_key and last.clm_key based on the previous latest months row.
sample data is below.]
data table1;
informat Notified_date sett_date rep_mnth ddmmyy10.;
format Notified_date sett_date rep_mnth ddmmyy10.;
input clm_key$ Notified_date sett_date sett_cnt out_cnt rep_mnth rep_mnthname $ Notif_year sett_year Notif_cnt reopen_cnt reopen_sett_cnt sett_reopnen_cnt;
cards;
CLM1 28/08/2014 21/02/2015 1 0 01/02/2015 Feb 2014 2015 . . 1 . .
CLM1 28/08/2014 29/08/2014 . 1 01/11/2014 Nov 2014 2014 . . . . .
CLM1 28/08/2014 29/08/2014 . 1 01/10/2014 Oct 2014 2014 . . . . .
CLM1 28/08/2014 29/08/2014 . 1 01/10/2014 Oct 2014 2014 . . . . .
CLM1 28/08/2014 29/08/2014 . 1 01/09/2014 Sep 2014 2014 . 1 . . .
CLM1 28/08/2014 29/08/2014 . 1 01/09/2014 Sep 2014 2014 . 1 . . .
CLM1 28/08/2014 29/08/2014 . 1 01/08/2014 Aug 2014 2014 1 . 1 . .
CLM2 28/08/2014 21/02/2015 1 0 01/02/2015 Feb 2014 2015 . . 1 .
CLM2 28/08/2014 21/02/2015 1 0 01/02/2015 Feb 2014 2015 . . 1 .
CLM2 28/08/2014 21/02/2015 1 0 01/02/2015 Feb 2014 2015 . . 1 .
CLM2 28/08/2014 29/08/2014 . 1 01/10/2014 Oct 2014 2014 . . . .
CLM2 28/08/2014 29/08/2014 . 1 01/10/2014 Oct 2014 2014 . . . .
CLM2 28/08/2014 29/08/2014 . 1 01/09/2014 Sep 2014 2014 . 1 . .
CLM2 28/08/2014 29/08/2014 . 1 01/08/2014 Aug 2014 2014 1 . 1 .
;
run;
expected output should have 25 rows with inserted rows as below.
clm_key | Notified_date | sett_date | sett_cnt | out_cnt | rep_mnth | rep_mnthname | Notif_year | sett_year | Notif_cnt | reopen_cnt | reopen_sett_cnt | sett_reopnen_cnt |
CLM1 | 28/08/2014 | 21/02/2015 | 1 | 0 | 01/02/2015 | Feb | 2014 | 2015 | . | . | 1 | . |
CLM1 | 28/08/2014 | 29/08/2014 | . | 1 | 01/01/2015 | Nov | 2014 | 2014 | . | . | . | . |
CLM1 | 28/08/2014 | 29/08/2014 | . | 1 | 01/12/2014 | Nov | 2014 | 2014 | . | . | . | . |
CLM1 | 28/08/2014 | 29/08/2014 | . | 1 | 01/11/2014 | Nov | 2014 | 2014 | . | . | . | . |
CLM1 | 28/08/2014 | 29/08/2014 | . | 1 | 01/01/2015 | Nov | 2014 | 2014 | . | . | . | . |
CLM1 | 28/08/2014 | 29/08/2014 | . | 1 | 01/12/2014 | Nov | 2014 | 2014 | . | . | . | . |
CLM1 | 28/08/2014 | 29/08/2014 | . | 1 | 01/11/2014 | Nov | 2014 | 2014 | . | . | . | . |
CLM1 | 28/08/2014 | 29/08/2014 | . | 1 | 01/10/2014 | Oct | 2014 | 2014 | . | . | . | . |
CLM1 | 28/08/2014 | 29/08/2014 | . | 1 | 01/10/2014 | Oct | 2014 | 2014 | . | . | . | . |
CLM1 | 28/08/2014 | 29/08/2014 | . | 1 | 01/09/2014 | Sep | 2014 | 2014 | . | 1 | . | . |
CLM1 | 28/08/2014 | 29/08/2014 | . | 1 | 01/09/2014 | Sep | 2014 | 2014 | . | 1 | . | . |
CLM1 | 28/08/2014 | 29/08/2014 | . | 1 | 01/08/2014 | Aug | 2014 | 2014 | 1 | . | 1 | . |
CLM2 | 28/08/2014 | 21/02/2015 | 1 | 0 | 01/02/2015 | Feb | 2014 | 2015 | . | . | 1 | . |
CLM2 | 28/08/2014 | 21/02/2015 | 1 | 0 | 01/02/2015 | Feb | 2014 | 2015 | . | . | 1 | . |
CLM2 | 28/08/2014 | 21/02/2015 | 1 | 0 | 01/02/2015 | Feb | 2014 | 2015 | . | . | 1 | . |
CLM2 | 28/08/2014 | 29/08/2014 | . | 1 | 01/01/2015 | Oct | 2014 | 2014 | . | . | . | . |
CLM2 | 28/08/2014 | 29/08/2014 | . | 1 | 01/01/2015 | Oct | 2014 | 2014 | . | . | . | . |
CLM2 | 28/08/2014 | 29/08/2014 | . | 1 | 01/12/2014 | Oct | 2014 | 2014 | . | . | . | . |
CLM2 | 28/08/2014 | 29/08/2014 | . | 1 | 01/12/2014 | Oct | 2014 | 2014 | . | . | . | . |
CLM2 | 28/08/2014 | 29/08/2014 | . | 1 | 01/11/2014 | Oct | 2014 | 2014 | . | . | . | . |
CLM2 | 28/08/2014 | 29/08/2014 | . | 1 | 01/11/2014 | Oct | 2014 | 2014 | . | . | . | . |
CLM2 | 28/08/2014 | 29/08/2014 | . | 1 | 01/10/2014 | Oct | 2014 | 2014 | . | . | . | . |
CLM2 | 28/08/2014 | 29/08/2014 | . | 1 | 01/10/2014 | Oct | 2014 | 2014 | . | . | . | . |
CLM2 | 28/08/2014 | 29/08/2014 | . | 1 | 01/09/2014 | Sep | 2014 | 2014 | . | 1 | . | . |
CLM2 | 28/08/2014 | 29/08/2014 | . | 1 | 01/08/2014 | Aug | 2014 | 2014 | 1 | . | 1 | . |
If you are licencing ETS, you could probably solve this with PROC EXPAND.
Else, try to create a lookup table with all date/id combinations (select distinct id - Cartesian join with your date range), and then full/left join with your original data.
You can adapt the following program to suit your exact needs
data not_quite_what_you_want(rename=(mnth=rep_mnth));
set table1;
by clm_key;
drop rep_mnth;
format mnth ddmmyy10.;
mnth=lag(rep_mnth);
if first.clm_key then do;
mnth=rep_mnth;
output;
end;
do while (mnth gt rep_mnth);
mnth=intnx("month",mnth,-1);
output;
if mnth ne rep_mnth then output;
end;
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.