How to create artificial rows in SAS dataset based on the latest observation by account number?

[sas_newbie94]

Hi anyone has any idea how to create additional rows in SAS dataset based on latest observation?

Here's my dataset:

data have;

    infile datalines missover;

    input accNo AMT yymm sequence;

    datalines;

    1 111663 1904 40

    2 120000 1807 50

    3 111986 1912 32

    4 108676 2005 20

    ;

run;

Table:

What I am trying to achieve is that for every account number if you notice the last record for yymm column is 1904 for accno 1. The sequence is 40. I would like to add additional rows with the same values but yymm will move by a month accordingly till date. The sequence will also move and + 1 accordingly. Please find desired output below. Take note that for yymm ending with 12, the yymm for example is 1912, the next one will be 2001. 

Desired Output:

 

[Kurt_Bremser]

First of all, ALWAYS (as in ALWAYS) store dates or date-related values as SAS dates.

Second, do NOT (as in NOT) use 2-digit years; they WILL come back and bite you in your behind sometime in the future.

Once you have made your data intelligent, the code is very simple, as you can use SAS tools for handling dates:

data have;
infile datalines missover;
input accNo AMT yymm :yymmn6. sequence;
format yymm yymmn6.;
datalines;
1 111663 201904 40
2 120000 201807 50
3 111986 201912 32
4 108676 202005 20
;

data want;
set have;
do until (yymm > today());
  output;
  yymm = intnx('month',yymm,1);
end;
run;

[ballardw · Accepted solution]

---

@sas_newbie94 wrote:

Hi anyone has any idea how to create additional rows in SAS dataset based on latest observation?

Here's my dataset:

data have;

    infile datalines missover;

    input accNo AMT yymm sequence;

    datalines;

    1 111663 1904 40

    2 120000 1807 50

    3 111986 1912 32

    4 108676 2005 20

    ;

run;

Table:

What I am trying to achieve is that for every account number if you notice the last record for yymm column is 1904 for accno 1. The sequence is 40. I would like to add additional rows with the same values but yymm will move by a month accordingly till date. The sequence will also move and + 1 accordingly. Please find desired output below. Take note that for yymm ending with 12, the yymm for example is 1912, the next one will be 2001. 

Desired Output:

 

---

Your "till date" does not make any sense as there is no "date". If you mean until the value of yymm is September of some year, it is not clear, that YY is 1920, 2020, 4420 or what year.

First thing. Create date values. Do no accept random numbers that you think represent a date because that will be much more difficult in the long run. When you have an actual SAS date value then there multiple display formats available, plus you can make your own, to display dates as needed. The function to  increment dates in INTNX.

You can provide different intervals to modify a date such as by 'day' 'month' 'quarter' or for time values by "second" "miniute" or "hour" to name a few.

data have;
    infile datalines missover;
    /* first read YYMM as a date value */
    input accNo AMT yymm :yymmn4. sequence;
    format yymm yymmn6.;
    datalines;
    1 111663 1904 40
    2 120000 1807 50
    3 111986 1912 32
    4 108676 2005 20
    ;
run;

data want;
   set have;
   /* send the current record to the output*/
   output; 
   do while (yymm lt '01SEP2020'd);
      yymm = intnx('month',yymm,1,'b');
      sequence=sequence+1;
      output;
   end;
run;



 

Having spent time verifying data and code for Y2K I personally refuse to do anything with 2-digit years.

https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/... has a PDF with much information about dates.

And if your YYMM variable is an simple numeric to convert it to a date value is

yymm  = input(put(yymm, 4.),yymmn4.);

assign a format

Format yymm yymmn6. ;

If you use real dates instead of silly codes then the value sort properly, make axis values in graphs space nicely, and as you can see the date functions know how to increment by a month properly without having to do lots of extra logic to deal with Dec to Jan and associated year issues.

To compare a value or set a literal date, such as the guessed 1Sep2020, the value is 'ddMONYYYY'd as shown in the code. That is the only way SAS knows that you intend the value to be a date. There are so many different ways of displaying date values with just digits that you can't tell what the intended date might be: 010203 for instance might be, depending on location, 02 Jan 2003, 01 Feb 2003, 03 Feb 2001.  And do you recognize this form of a date:  20023   (23 Jan 2020 or 1920 or some other year that ends in 20).

[AlanC]

Agreed on switching to a single value vs split. Not sure why it would be split, actually.

One clarification to the above posts. SAS does not have a 'date' value. It only understands characters and numbers. It can interpret a number as a date but it has no idea if something is a date or a number until you tell it.

Today, as a date for example, is 22194 which is the number of days since Jan 1, 1960. If you constrain a value between a certain range, 10000-20000 say, you will get the numbers needed to display dates in that range. If you want to add a day, add 1. 

See: http://dictionary.sensagent.com/epochal%20date/en-en/