How do I add a row number to a table in SAS code?

[MariaAguirre]

Hi!

We need to add rows in a sas dataset, the number of rows depends on the difference between two columns and is variable according to an id. 

Attached is an example in excel, in yellow are the rows needed to be add. 

Thanks!!!

[PeterClemmensen]

What two columns are you referencing? You have to be more specific than this if you want a usable answer.

[Reeza]

Your subject line and question don’t align. What’s the logic for adding those rows? When do you add? How do you know? Show several variations of your logic please.

[MariaAguirre]

The original dataset have the number of rows of the number of payments till the basedate. I need to have rows equal de number of payments . In the example attached there are 18 payments but only 16 rows, so I need to add two more rows. 

The number of rows added will depend of the total number of payments and the maximun payment I see. 

[Reeza]

Ok. Please provide sample data as text, so we can work with it. If you cannot, please make some fake data. 

[MariaAguirre]

Hi!

Attached is an example, the column "cuotas_plan" the total number of payments. The column Nro_Cuota_Plan are the number of payments I see, in the first case there are 12 payments and I see only 3.

Thanks!!

[Tom]

If the one field says there are 12 payments and only 3 appear in the data what logic do your propose to make up records for the other 9 payments?  What values shoudl the new records have for the other fields?

Also do the numbers that exist always go in order like in your example?  Or could the 3 payments be numbers 4,7 and 8 of 12 instead of just 1,2 and 3 of 12?

[Reeza]

What happens to the other values? Are they retained? Especially the last column that seems to change.

You can use output and BY group processing.

1. Set up BY list to specify your groups correctly

2. At last of each group, check if nro_cuota_plan is less than cuotas_plan. If so, output records and increement nro_cuota_plan until it's full. Otherwise, write same record out.

This is untested, but you should be able to get this working from here. 

data outputData;
set inputData;
*this is very important;
by <list variables to uniquely identify a group>;

*last variable in the by statement indicates a new group;
if last.var and nro_cuota_plan < cuotas_plan then do ; 
output;
do i=nro_cuota_plan+1 to cuotos_plan;
nro_cuota_plan+1;
output;
end;
end;
else output;

run;

---

@MariaAguirre wrote:

Hi!

Attached is an example, the column "cuotas_plan" the total number of payments. The column Nro_Cuota_Plan are the number of payments I see, in the first case there are 12 payments and I see only 3.

Thanks!!

---

[MariaAguirre]

Hi! The new rows will be the "nro_plan_cuotas" I don´t have.

I tried the example and it didn´t work...

[Reeza]

Post the code you tried. I did state you needed to complete some portions which I assume you did.

---

@MariaAguirre wrote:

Hi! The new rows will be the "nro_plan_cuotas" I don´t have.

I tried the example and it didn´t work...

---

[MariaAguirre]

data outputData;
set work.example;
*this is very important;
by contrato_id;

*last variable in the by statement indicates a new group;
if last.var and nro_cuota_plan < cuotas_plan then do ; 
output;
do i=nro_cuota_plan+1 to cuotas_plan;
nro_cuota_plan+1;
output;
end;
end;
else output;

run;

[Reeza]

if last.var -> this needs to reference your variable.
SO it should be
if last.contrato_id. Then it works fine.

Please read up on BY group processing, FIRST/LAST automatic variables so you understand what's happening.

[MariaAguirre]

Thanks! 

[Tom]

Your FIRST. reference and BY statements need match.

Also you could probably eliminate the extra variable I and just use your other variable in the DO loop.

But I suspect that do will want to set one or more of the variables to missing for these extra observations, depending on their meaning.

data outputData;
  set work.example;
  by contrato_id;
  output;
  if last.contrato_id then do nro_cuota_plan=nro_cuota_plan+1 to coutas_plan;
* Should any of the variable be set missing? ;
*   call missing(x , y) ;
    output;
  end;
run;

[MariaAguirre]

Thanks!!