topic Re: Calculate variable in different rows and create a new variable. in SAS Programming

Calculate variable in different rows and create a new variable.

cnn96 — Mon, 17 Apr 2023 11:35:57 GMT

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.

Re: Calculate variable in different rows and create a new variable.

ErikLund_Jensen — Mon, 17 Apr 2023 11:46:55 GMT

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;

Re: Calculate variable in different rows and create a new variable.

Ksharp — Mon, 17 Apr 2023 11:52:32 GMT

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

Re: Calculate variable in different rows and create a new variable.

AhmedAl_Attar — Mon, 17 Apr 2023 12:09:28 GMT

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;

Re: Calculate variable in different rows and create a new variable.

cnn96 — Mon, 17 Apr 2023 12:35:04 GMT

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