## Calculate time of follow-up in cohort.

Solved
Frequent Contributor
Posts: 84

# Calculate time of follow-up in cohort.

Hello,

I very much appreciate someone create SAS code for me.

I am looking time of follow-up in cohort. Here, I included 3 subjects.

Phan S.

Data have;

input id s_date s_year;

informat s_date date9.;

format s_date date9.;

cards;

1       03APR2008         2008

1       24NOV2009         2009

1       14SEP2010         2010

1       12AUG2011         2011

3       04OCT2007         2007

3       03JUN2009         2009

3       28JAN2011         2011

8       24DEC2008         2008

8       21DEC2009         2009

;

run;

proc print data=have;

run;

proc sort data=have out=want;

by id;

run;

data time;

set want;

by id;

format s_date first_sdt last_sdt date9.;

if first.id then do;

if s_date then first_sdt=s_date;

end;

if last.id then do;

last_sdt = s_date;

end;

run;

I got output - that I cannot compute time of follow-up.

Obs    id       s_date    first_sdt     last_sdt

1      1    03APR2008    03APR2008            .

2      1    24NOV2009            .            .

3      1    14SEP2010            .            .

4      1    12AUG2011            .    12AUG2011

5      3    04OCT2007    04OCT2007            .

6      3    03JUN2009            .            .

7      3    28JAN2011            .    28JAN2011

8      8    24DEC2008    24DEC2008            .

9      8    21DEC2009            .    21DEC2009

Accepted Solutions
Solution
‎06-13-2013 11:56 AM
Super Contributor
Posts: 1,636

## Re: Calculate time of follow-up in cohort.

Data have;
input id s_date s_year;
informat s_date date9.;
format s_date date9.;

cards;
1       03APR2008         2008
1       24NOV2009         2009
1       14SEP2010         2010
1       12AUG2011         2011
3       04OCT2007         2007
3       03JUN2009         2009
3       28JAN2011         2011
8       24DEC2008         2008
8       21DEC2009         2009
;

run;

proc print data=have;
run;

proc sort data=have out=want;
by id;
run;

data time;
retain id first_sdt last_sdt;
set want;
by id;
format s_date first_sdt last_sdt date9.;
if first.id then do;
if s_date then first_sdt=s_date;
end;
if last.id then do;
last_sdt = s_date; follow_up=last_sdt-first_sdt; output;
end;
run;
proc print;run;

follow_
Obs    id    first_sdt     last_sdt       s_date    s_year       up

1      1    03APR2008    12AUG2011    12AUG2011     2011       1226
2      3    04OCT2007    28JAN2011    28JAN2011     2011       1212
3      8    24DEC2008    21DEC2009    21DEC2009     2009        362

All Replies
Super User
Posts: 23,683

## Re: Calculate time of follow-up in cohort.

proc sql;

create table want as

select id, min(s_date) as start_date, max(s_date) as end_date, max(s_date)-min(s_date) as time_followup;

from have

group by id

order by id;

quit;

Frequent Contributor
Posts: 84

## Re: Calculate time of follow-up in cohort.

Dear Reeza,

I think you come-up with a very nice solution. There is an error in log file - please review and fix for me - I never use proc sql.

Thanks.

Phan S.

1742  proc sql;
1743
1744  create table want as
1745
1746  select id, min(s_date) as start_date, max(s_date) as end_date,
1746! max(s_date)-min(s_date) as time_followup,
1747
1748  from have
----
22
76
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!,
&, (, *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?, AND, AS,
BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN,
INFORMAT, INTO, IS, LABEL, LE, LEN, LENGTH, LET, LIKE, LT, LTT, NE,
NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

1749
1750  group by id
1751
1752  order by id;
1753
1754  quit;

Super Contributor
Posts: 1,636

## Re: Calculate time of follow-up in cohort.

delete the red ',' from     max(s_date)-min(s_date) as time_followup,

Solution
‎06-13-2013 11:56 AM
Super Contributor
Posts: 1,636

## Re: Calculate time of follow-up in cohort.

Data have;
input id s_date s_year;
informat s_date date9.;
format s_date date9.;

cards;
1       03APR2008         2008
1       24NOV2009         2009
1       14SEP2010         2010
1       12AUG2011         2011
3       04OCT2007         2007
3       03JUN2009         2009
3       28JAN2011         2011
8       24DEC2008         2008
8       21DEC2009         2009
;

run;

proc print data=have;
run;

proc sort data=have out=want;
by id;
run;

data time;
retain id first_sdt last_sdt;
set want;
by id;
format s_date first_sdt last_sdt date9.;
if first.id then do;
if s_date then first_sdt=s_date;
end;
if last.id then do;
last_sdt = s_date; follow_up=last_sdt-first_sdt; output;
end;
run;
proc print;run;

follow_
Obs    id    first_sdt     last_sdt       s_date    s_year       up

1      1    03APR2008    12AUG2011    12AUG2011     2011       1226
2      3    04OCT2007    28JAN2011    28JAN2011     2011       1212
3      8    24DEC2008    21DEC2009    21DEC2009     2009        362

Frequent Contributor
Posts: 84

## Re: Calculate time of follow-up in cohort.

Dear Linlin,

It is beautiful. Thanks.

I wonder this format is wide set. Isn't it. I need to perform other analyses that I prefer to keep in the long format.

Do you have another solution - much appreciate.

I slightly changes yours codes - to get year.

Phan S.

proc sort data=have out=want;
by id;
run;

data time;
retain id first_sdt last_sdt;
set want;
by id;
format s_date first_sdt last_sdt date9.;
if first.id then do;
if s_date then first_sdt=s_date;
end;
if last.id then do;

last_sdt = s_date; follow_up=round ((last_sdt-first_sdt) / 365.,1); output;

end;
run;
proc print; run;

follow_
Obs    id    first_sdt     last_sdt       s_date    s_year       up

1      1    03APR2008    12AUG2011    12AUG2011     2011        3
2      3    04OCT2007    28JAN2011    28JAN2011     2011        3
3      8    24DEC2008    21DEC2009    21DEC2009     2009        1

Super Contributor
Posts: 1,636

## Re: Calculate time of follow-up in cohort.

what your "WANT" dataset should be?

Frequent Contributor
Posts: 84

## Re: Calculate time of follow-up in cohort.

Hi Linli,

In a long format. Otherwise, I have to re-merge data sets.

Is it possible?

Phan S.

Super Contributor
Posts: 1,636

## Re: Calculate time of follow-up in cohort.

is this what you want?

Data have;
input id s_date s_year;
informat s_date date9.;
format s_date date9.;
cards;
1       03APR2008         2008
1       24NOV2009         2009
1       14SEP2010         2010
1       12AUG2011         2011
3       04OCT2007         2007
3       03JUN2009         2009
3       28JAN2011         2011
8       24DEC2008         2008
8       21DEC2009         2009
;

proc sort data=have out=want;
by id s_date;
run;

data time;
do _n_ = 1 by 1 until (last.id) ;
set want ;
by id ;
if first.id then first_sdt=s_date;
if last.id then last_sdt=s_date;
follow_up=last_sdt-first_sdt;
end ;
do until (last.id) ;
set want;
by id ;
output ;
end ;
format first_sdt last_sdt date9.;
run;
proc print data=time;run;

follow_
Obs    id       s_date    s_year       first_sdt              last_sdt       up

1      1    03APR2008     2008     03APR2008    12AUG2011      1226
2      1    24NOV2009     2009     03APR2008    12AUG2011      1226
3      1    14SEP2010     2010     03APR2008    12AUG2011      1226
4      1    12AUG2011     2011     03APR2008    12AUG2011      1226
5      3    04OCT2007     2007     04OCT2007    28JAN2011      1212
6      3    03JUN2009     2009     04OCT2007    28JAN2011      1212
7      3    28JAN2011     2011     04OCT2007    28JAN2011      1212
8      8    24DEC2008     2008     24DEC2008    21DEC2009       362
9      8    21DEC2009     2009     24DEC2008    21DEC2009       362

Frequent Contributor
Posts: 84

## Re: Calculate time of follow-up in cohort.

Linli,

Perfect !

I want to have a decimal when converting to years or months.

But this seems not correct.

time = round (time/365.,1);

Phan S.

Super Contributor
Posts: 1,636

## Re: Calculate time of follow-up in cohort.

try:

data time;

do _n_ = 1 by 1 until (last.id) ;

set want ;

by id ;

if first.id then first_sdt=s_date;

if last.id then last_sdt=s_date;

follow_up=last_sdt-first_sdt;

year=round(follow_up/365,.1);

end ;

do until (last.id) ;

set want;

by id ;

output ;

end ;

format first_sdt last_sdt date9.;

run;

proc print data=time;run;

Frequent Contributor
Posts: 84

## Re: Calculate time of follow-up in cohort.

Hi Linli,

It was solving ! Many thanks for your help.

Phan S.

Frequent Contributor
Posts: 84

## Re: Calculate time of follow-up in cohort.

Dear Linli,

Thanks.

Phan S.

Frequent Contributor
Posts: 84

## Re: Calculate time of follow-up in cohort.

Dear Linli,

I think SAS community should give options 'correct answer' to more than one person.

Phan S.

Super User
Posts: 23,683

## Re: Calculate time of follow-up in cohort.

Why did you give yourself the correct answer? It should be LinLin's over that one.

🔒 This topic is solved and locked.