topic Re: How do I add a row number to a table in SAS code? in New SAS User

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

MariaAguirre — Thu, 27 Sep 2018 18:54:27 GMT

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!!!

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

PeterClemmensen — Thu, 27 Sep 2018 19:58:25 GMT

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

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

Reeza — Thu, 27 Sep 2018 20:02:15 GMT

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.

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

MariaAguirre — Thu, 27 Sep 2018 20:08:48 GMT

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.

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

Reeza — Fri, 28 Sep 2018 00:53:57 GMT

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

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

MariaAguirre — Fri, 28 Sep 2018 13:48:06 GMT

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!!

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

Tom — Fri, 28 Sep 2018 14:43:26 GMT

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?

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

Reeza — Fri, 28 Sep 2018 15:34:54 GMT

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!!

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

MariaAguirre — Fri, 28 Sep 2018 18:23:32 GMT

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

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

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

Reeza — Fri, 28 Sep 2018 18:36:24 GMT

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...

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

MariaAguirre — Fri, 28 Sep 2018 18:50:58 GMT

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;

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

Reeza — Fri, 28 Sep 2018 19:13:31 GMT

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.

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

Tom — Fri, 28 Sep 2018 19:18:40 GMT

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;

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

MariaAguirre — Fri, 28 Sep 2018 20:32:20 GMT

Thanks!

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

MariaAguirre — Fri, 28 Sep 2018 20:32:40 GMT

Thanks!!