Duplicate latest record by id until max date

sivakoya — Tue, 24 Jan 2017 02:41:06 GMT

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

Re: Duplicate latest record by id until max date

mkeintz — Tue, 24 Jan 2017 04:45:37 GMT

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;

topic Re: Duplicate latest record by id until max date in SAS Procedures

Duplicate latest record by id until max date

Re: Duplicate latest record by id until max date