☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Fluorite | Level 6

## Calculate variable in different rows and create a new variable.

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Rhodochrosite | Level 12

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

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;
4 REPLIES 4
Rhodochrosite | Level 12

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

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;``````
Super User

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

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

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

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;
Fluorite | Level 6

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

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

Discussion stats
• 4 replies
• 379 views
• 3 likes
• 4 in conversation