CODEWITHSUNDEEP
sas
Neoleogeo
Neoleogeo

Reputation: 323

adding rows given a certain condition

I have a database with 3 columns. ID, Date and amount. It is ordered by ID and Date. All I want to do is to add a row after the latest occurrence of every ID with the same ID, Date = Date + 1 Month and Amount = 0.

As an Illustration I want to go from this:

id      | Date  |amount |
A       |  01JAN|   1   |
A       |  01FEB|   1   |  
B       |  01FEB|   0   |
B       |  01MAR|   1   |

to this:

id      | Date  |amount |
A       |  01JAN|   1   |
A       |  01FEB|   1   |  
A       |  01MAR|   0   | <- ADD THIS ROW  
B       |  01FEB|   0   |
B       |  01MAR|   1   |
B       |  01APR|   0   |<- ADD THIS ROW

I know I should use intxn but beyond that I don't really know what to do. I appreciate any input.

Upvotes: 0

Views: 300

Answers (1)

Tom
Tom

Reputation: 51566

Assuming that the DATE variable has actual date values in it you just need to output twice on the last observation in each group.

data want;
  set have;
  by id;
  output;
  if last.id then do;
    date=intnx('month',date,1,'b');
    amount=0;
    output;
  end;
run;

Upvotes: 2

Related Questions