topic Re: Insert duplicate rows based on latest months in SAS Programming https://communities.sas.com/t5/SAS-Programming/Insert-duplicate-rows-based-on-latest-months/m-p/341565#M78205 I don't get the logic behind this. For CLM1, you fill the missing rows between February 2015 and November 2014. But why are those three lines repeated below ? Thu, 16 Mar 2017 13:22:51 GMT gamotte 2017-03-16T13:22:51Z Insert duplicate rows based on latest months https://communities.sas.com/t5/SAS-Programming/Insert-duplicate-rows-based-on-latest-months/m-p/341446#M78152 <P>Hi ,</P><P>&nbsp;</P><P>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.</P><P>&nbsp;</P><P>sample data is below.]</P><P>&nbsp;</P><P><FONT color="#000080" face="Courier New" size="2"><STRONG>data</STRONG></FONT><FONT face="Courier New" size="2"> table1;</FONT></P><P><FONT color="#0000ff" face="Courier New" size="2">informat</FONT><FONT face="Courier New" size="2"> Notified_date sett_date rep_mnth </FONT><FONT color="#008080" face="Courier New" size="2">ddmmyy10.</FONT><FONT face="Courier New" size="2">;</FONT></P><P><FONT color="#0000ff" face="Courier New" size="2">format</FONT><FONT face="Courier New" size="2"> Notified_date sett_date rep_mnth </FONT><FONT color="#008080" face="Courier New" size="2">ddmmyy10.</FONT><FONT face="Courier New" size="2">;</FONT></P><P><FONT color="#0000ff" face="Courier New" size="2">input</FONT><FONT face="Courier New" size="2"> 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;</FONT></P><P><FONT color="#0000ff" face="Courier New" size="2">cards</FONT><FONT face="Courier New" size="2">;</FONT></P><P>CLM1 28/08/2014 21/02/2015 1 0 01/02/2015 Feb 2014 2015 . . 1 . .</P><P>CLM1 28/08/2014 29/08/2014 . 1 01/11/2014 Nov 2014 2014 . . . . .</P><P>CLM1 28/08/2014 29/08/2014 . 1 01/10/2014 Oct 2014 2014 . . . . .</P><P>CLM1 28/08/2014 29/08/2014 . 1 01/10/2014 Oct 2014 2014 . . . . .</P><P>CLM1 28/08/2014 29/08/2014 . 1 01/09/2014 Sep 2014 2014 . 1 . . .</P><P>CLM1 28/08/2014 29/08/2014 . 1 01/09/2014 Sep 2014 2014 . 1 . . .</P><P>CLM1 28/08/2014 29/08/2014 . 1 01/08/2014 Aug 2014 2014 1 . 1 . .</P><P>CLM2 28/08/2014 21/02/2015 1 0 01/02/2015 Feb 2014 2015 . . 1 .</P><P>CLM2 28/08/2014 21/02/2015 1 0 01/02/2015 Feb 2014 2015 . . 1 .</P><P>CLM2 28/08/2014 21/02/2015 1 0 01/02/2015 Feb 2014 2015 . . 1 .</P><P>CLM2 28/08/2014 29/08/2014 . 1 01/10/2014 Oct 2014 2014 . . . .</P><P>CLM2 28/08/2014 29/08/2014 . 1 01/10/2014 Oct 2014 2014 . . . .</P><P>CLM2 28/08/2014 29/08/2014 . 1 01/09/2014 Sep 2014 2014 . 1 . .</P><P>CLM2 28/08/2014 29/08/2014 . 1 01/08/2014 Aug 2014 2014 1 . 1 .</P><P>;</P><P><FONT color="#000080" face="Courier New" size="2"><STRONG>run</STRONG></FONT><FONT face="Courier New" size="2">;</FONT></P><P>&nbsp;</P><P><FONT face="Courier New" size="2">expected output should have&nbsp;25 rows with inserted rows as below.</FONT></P><P>&nbsp;</P><TABLE><TBODY><TR><TD>clm_key</TD><TD>Notified_date</TD><TD>sett_date</TD><TD>sett_cnt</TD><TD>out_cnt</TD><TD>rep_mnth</TD><TD>rep_mnthname</TD><TD>Notif_year</TD><TD>sett_year</TD><TD>Notif_cnt</TD><TD>reopen_cnt</TD><TD>reopen_sett_cnt</TD><TD>sett_reopnen_cnt</TD></TR><TR><TD>CLM1</TD><TD>28/08/2014</TD><TD>21/02/2015</TD><TD>1</TD><TD>0</TD><TD>01/02/2015</TD><TD>Feb</TD><TD>2014</TD><TD>2015</TD><TD>.</TD><TD>.</TD><TD>1</TD><TD>.</TD></TR><TR><TD>CLM1</TD><TD>28/08/2014</TD><TD>29/08/2014</TD><TD>.</TD><TD>1</TD><TD>01/01/2015</TD><TD>Nov</TD><TD>2014</TD><TD>2014</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD></TR><TR><TD>CLM1</TD><TD>28/08/2014</TD><TD>29/08/2014</TD><TD>.</TD><TD>1</TD><TD>01/12/2014</TD><TD>Nov</TD><TD>2014</TD><TD>2014</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD></TR><TR><TD>CLM1</TD><TD>28/08/2014</TD><TD>29/08/2014</TD><TD>.</TD><TD>1</TD><TD>01/11/2014</TD><TD>Nov</TD><TD>2014</TD><TD>2014</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD></TR><TR><TD>CLM1</TD><TD>28/08/2014</TD><TD>29/08/2014</TD><TD>.</TD><TD>1</TD><TD>01/01/2015</TD><TD>Nov</TD><TD>2014</TD><TD>2014</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD></TR><TR><TD>CLM1</TD><TD>28/08/2014</TD><TD>29/08/2014</TD><TD>.</TD><TD>1</TD><TD>01/12/2014</TD><TD>Nov</TD><TD>2014</TD><TD>2014</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD></TR><TR><TD>CLM1</TD><TD>28/08/2014</TD><TD>29/08/2014</TD><TD>.</TD><TD>1</TD><TD>01/11/2014</TD><TD>Nov</TD><TD>2014</TD><TD>2014</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD></TR><TR><TD>CLM1</TD><TD>28/08/2014</TD><TD>29/08/2014</TD><TD>.</TD><TD>1</TD><TD>01/10/2014</TD><TD>Oct</TD><TD>2014</TD><TD>2014</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD></TR><TR><TD>CLM1</TD><TD>28/08/2014</TD><TD>29/08/2014</TD><TD>.</TD><TD>1</TD><TD>01/10/2014</TD><TD>Oct</TD><TD>2014</TD><TD>2014</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD></TR><TR><TD>CLM1</TD><TD>28/08/2014</TD><TD>29/08/2014</TD><TD>.</TD><TD>1</TD><TD>01/09/2014</TD><TD>Sep</TD><TD>2014</TD><TD>2014</TD><TD>.</TD><TD>1</TD><TD>.</TD><TD>.</TD></TR><TR><TD>CLM1</TD><TD>28/08/2014</TD><TD>29/08/2014</TD><TD>.</TD><TD>1</TD><TD>01/09/2014</TD><TD>Sep</TD><TD>2014</TD><TD>2014</TD><TD>.</TD><TD>1</TD><TD>.</TD><TD>.</TD></TR><TR><TD>CLM1</TD><TD>28/08/2014</TD><TD>29/08/2014</TD><TD>.</TD><TD>1</TD><TD>01/08/2014</TD><TD>Aug</TD><TD>2014</TD><TD>2014</TD><TD>1</TD><TD>.</TD><TD>1</TD><TD>.</TD></TR><TR><TD>CLM2</TD><TD>28/08/2014</TD><TD>21/02/2015</TD><TD>1</TD><TD>0</TD><TD>01/02/2015</TD><TD>Feb</TD><TD>2014</TD><TD>2015</TD><TD>.</TD><TD>.</TD><TD>1</TD><TD>.</TD></TR><TR><TD>CLM2</TD><TD>28/08/2014</TD><TD>21/02/2015</TD><TD>1</TD><TD>0</TD><TD>01/02/2015</TD><TD>Feb</TD><TD>2014</TD><TD>2015</TD><TD>.</TD><TD>.</TD><TD>1</TD><TD>.</TD></TR><TR><TD>CLM2</TD><TD>28/08/2014</TD><TD>21/02/2015</TD><TD>1</TD><TD>0</TD><TD>01/02/2015</TD><TD>Feb</TD><TD>2014</TD><TD>2015</TD><TD>.</TD><TD>.</TD><TD>1</TD><TD>.</TD></TR><TR><TD>CLM2</TD><TD>28/08/2014</TD><TD>29/08/2014</TD><TD>.</TD><TD>1</TD><TD>01/01/2015</TD><TD>Oct</TD><TD>2014</TD><TD>2014</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD></TR><TR><TD>CLM2</TD><TD>28/08/2014</TD><TD>29/08/2014</TD><TD>.</TD><TD>1</TD><TD>01/01/2015</TD><TD>Oct</TD><TD>2014</TD><TD>2014</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD></TR><TR><TD>CLM2</TD><TD>28/08/2014</TD><TD>29/08/2014</TD><TD>.</TD><TD>1</TD><TD>01/12/2014</TD><TD>Oct</TD><TD>2014</TD><TD>2014</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD></TR><TR><TD>CLM2</TD><TD>28/08/2014</TD><TD>29/08/2014</TD><TD>.</TD><TD>1</TD><TD>01/12/2014</TD><TD>Oct</TD><TD>2014</TD><TD>2014</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD></TR><TR><TD>CLM2</TD><TD>28/08/2014</TD><TD>29/08/2014</TD><TD>.</TD><TD>1</TD><TD>01/11/2014</TD><TD>Oct</TD><TD>2014</TD><TD>2014</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD></TR><TR><TD>CLM2</TD><TD>28/08/2014</TD><TD>29/08/2014</TD><TD>.</TD><TD>1</TD><TD>01/11/2014</TD><TD>Oct</TD><TD>2014</TD><TD>2014</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD></TR><TR><TD>CLM2</TD><TD>28/08/2014</TD><TD>29/08/2014</TD><TD>.</TD><TD>1</TD><TD>01/10/2014</TD><TD>Oct</TD><TD>2014</TD><TD>2014</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD></TR><TR><TD>CLM2</TD><TD>28/08/2014</TD><TD>29/08/2014</TD><TD>.</TD><TD>1</TD><TD>01/10/2014</TD><TD>Oct</TD><TD>2014</TD><TD>2014</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD></TR><TR><TD>CLM2</TD><TD>28/08/2014</TD><TD>29/08/2014</TD><TD>.</TD><TD>1</TD><TD>01/09/2014</TD><TD>Sep</TD><TD>2014</TD><TD>2014</TD><TD>.</TD><TD>1</TD><TD>.</TD><TD>.</TD></TR><TR><TD>CLM2</TD><TD>28/08/2014</TD><TD>29/08/2014</TD><TD>.</TD><TD>1</TD><TD>01/08/2014</TD><TD>Aug</TD><TD>2014</TD><TD>2014</TD><TD>1</TD><TD>.</TD><TD>1</TD><TD>.</TD></TR></TBODY></TABLE> Thu, 16 Mar 2017 07:34:09 GMT https://communities.sas.com/t5/SAS-Programming/Insert-duplicate-rows-based-on-latest-months/m-p/341446#M78152 Reddi 2017-03-16T07:34:09Z Re: Insert duplicate rows based on latest months https://communities.sas.com/t5/SAS-Programming/Insert-duplicate-rows-based-on-latest-months/m-p/341500#M78187 <P>If you are licencing ETS, you could probably solve this with PROC EXPAND.</P> <P>Else, try to create&nbsp;a lookup table&nbsp;with all date/id combinations&nbsp;(select distinct id - Cartesian&nbsp;join with your date&nbsp;range), and then full/left join with your original data.</P> Thu, 16 Mar 2017 09:38:59 GMT https://communities.sas.com/t5/SAS-Programming/Insert-duplicate-rows-based-on-latest-months/m-p/341500#M78187 LinusH 2017-03-16T09:38:59Z Re: Insert duplicate rows based on latest months https://communities.sas.com/t5/SAS-Programming/Insert-duplicate-rows-based-on-latest-months/m-p/341565#M78205 I don't get the logic behind this. For CLM1, you fill the missing rows between February 2015 and November 2014. But why are those three lines repeated below ? Thu, 16 Mar 2017 13:22:51 GMT https://communities.sas.com/t5/SAS-Programming/Insert-duplicate-rows-based-on-latest-months/m-p/341565#M78205 gamotte 2017-03-16T13:22:51Z Re: Insert duplicate rows based on latest months https://communities.sas.com/t5/SAS-Programming/Insert-duplicate-rows-based-on-latest-months/m-p/341573#M78206 <P>You can adapt the following program to suit your exact needs</P> <P>&nbsp;</P> <PRE><CODE class=" language-sas">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;</CODE></PRE> Thu, 16 Mar 2017 13:34:08 GMT https://communities.sas.com/t5/SAS-Programming/Insert-duplicate-rows-based-on-latest-months/m-p/341573#M78206 gamotte 2017-03-16T13:34:08Z