Duplicate latest record by id until max date

[sivakoya]

Hi,

I have following monthly dataset

month_end   account_nbr     x     y     z

30sep16               1            100    Y    1

31oct16                1             50     Y    1

30nov16               1             25     Y    1

31dec16               1             10     Y    1

30sep16               2            100    Y    1

31oct16                2             50     Y    1

30nov16               2             0       Y    1

30oct16                3             0       Y    1

Account_nbr '2' is closed in nov and monthly dataset will not have a record for 2 in dec.

This is a huge dataset with millions of records across several years. I am looking to create a dummy row for account_nbr '2' for the latest month end i.e 31dec16 with the values from nov. For acct_nbr '3' nov month end record too, for that I would need to create records for nov and dec. since dec is the latest month end in my dataset. Please suggest a easy way to do this.

Resulting dataset should look like:

month_end   account_nbr     x     y     z

30sep16               1            100    Y    1

31oct16                1             50     Y    1

30nov16               1             25     Y    1

31dec16               1             10     Y    1

30sep16               2            100    Y    1

31oct16                2             50     Y    1

30nov16               2             0       Y    1

31dec16               2             0       Y    1

30oct16                3             0       Y    1

30Nov16               3             0       Y    1

30Dec16               3             0       Y    1

Thanks

[sivakoya]

I have following monthly dataset

month_end   account_nbr     x     y     z

30sep16               1            100    Y    1

31oct16                1             50     Y    1

30nov16               1             25     Y    1

31dec16               1             10     Y    1

30sep16               2            100    Y    1

31oct16                2             50     Y    1

30nov16               2             0       Y    1

30oct16                3             0       Y    1

Account_nbr '2' is closed in nov and monthly dataset will not have a record for 2 in dec.

This is a huge dataset with millions of records across several years. I am looking to create a dummy row for account_nbr '2' for the latest month end i.e 31dec16 with the values from nov. For acct_nbr '3' nov month end record too, for that I would need to create records for nov and dec. since dec is the latest month end in my dataset. Please suggest a easy way to do this.

Resulting dataset should look like:

month_end   account_nbr     x     y     z

30sep16               1            100    Y    1

31oct16                1             50     Y    1

30nov16               1             25     Y    1

31dec16               1             10     Y    1

30sep16               2            100    Y    1

31oct16                2             50     Y    1

30nov16               2             0       Y    1

31dec16               2             0       Y    1

30oct16                3             0       Y    1

30Nov16               3             0       Y    1

30Dec16               3             0       Y    1

Thanks

[mkeintz]

The benefit of a SET statement  coupled with a BY statement is that you know when you've reached the last record for a by group.  And if that last record precedes 31dec2016, you also know to output more records:

data want;
  set have;
  by account_nbr;
  output;

  if last.account_nbr and month_end<'31dec2016'd then do until (month_end='31dec2016'd);
    month_end=intnx('month',month_end,1,'e');
    output;
  end;
run;