Calculate variable in different rows and create a new variable.

[cnn96]

Hello all.

I would like to complete the table on the left side with a variable time span based on the years.

I hope that you can help me.

[ErikLund_Jensen]

Hi @cnn96 

I think this will do it.

First use a select to calculate the time_span for each PatientID then add the calculated value with a join. 

Note: Untested, because you didn't provide a data step to generate test data. 

proc sql;
  create table b as
    select distinct PatientID, count(*) as time_span
    from mylib.mytable
    group by PatientID;
quit;

proc sql;
  create table table_timespan as
    select a.*, b.time_span
    from mylib.mytable as a, b
    on a.PatientID = b.PatientID;
quit;

[Ksharp]

proc sql;
create table want as
select id,year,range(year) as time_span
from have
group by id ;
quit;

[AhmedAl_Attar · Accepted solution]

To get the time_span numbers listed in the "Aim:", I think the query should be
proc sql;
create table want as
select id,year,(range(year)+1) as time_span
from have
group by id ;
quit;

[cnn96]

Thank you very much. This way it worked the way i wanted it to.😊