BookmarkSubscribeRSS Feed
sivakoya
Obsidian | Level 7

 

 

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

 

1 REPLY 1
mkeintz
PROC Star

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;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 905 views
  • 1 like
  • 2 in conversation