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
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.