Calculate time of follow-up in cohort.

Accepted Solution Solved
Reply
Contributor
Posts: 43
Accepted Solution

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.

Thanks in advance.

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

View solution in original post


All Replies
Super User
Posts: 17,730

Re: Calculate time of follow-up in cohort.

Use Proc SQL instead.

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;

Contributor
Posts: 43

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;

https://mail.google.com/mail/u/0/images/cleardot.gif

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

Contributor
Posts: 43

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?

Contributor
Posts: 43

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

Contributor
Posts: 43

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;

Contributor
Posts: 43

Re: Calculate time of follow-up in cohort.

Hi Linli,

It was solving ! Many thanks for your help.

Phan S.

Contributor
Posts: 43

Re: Calculate time of follow-up in cohort.

Dear Linli,

Thanks.

Phan S.

Contributor
Posts: 43

Re: Calculate time of follow-up in cohort.

Dear Linli,

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

Your solution should receive a credit as 'Correct answer' as well.

Phan S.

Super User
Posts: 17,730

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 17 replies
  • 877 views
  • 6 likes
  • 3 in conversation