DATA Step, Macro, Functions and more

Define exact time period

Reply
Frequent Contributor
Posts: 82

Define exact time period

Dear all,

I have an excel file list below:

ID   B                  visit_date
46 SCREEN      10/27/2008
46 MONTH06    4/23/2009
46 FLOW          10/27/2008
46 MONTH12    10/26/2009
46 FLOW            4/23/2009
46 FLOW            12/3/2009
46 MONTH18     6/10/2010
46 FLOW            1/6/2011
46 MONTH24     1/6/2011
53 SCREEN       12/19/2008

53 MONTH06     1/26/2009

Based on visit_date of screen (B variable), I want to know if visit_date of month06, month12, month18, and month24 is within a range of +/- 3 months for each ID.

if the visit_date of month06, 12, 18, or 24  is out of 3 months range, it should be renamed as below or above.

Any help will be appreciated.

Respected Advisor
Posts: 3,124

Re: Define exact time period

if I understand what you asked, and if your presented sample data has exhausted all of your data scenarios, and the 'SCREEN' is always the first for each ID, then the following code could get you started:

data have;

     input

           ID   B :$10. visit_date :mmddyy10.;

     format  visit_date mmddyy10.;

     cards;

46 SCREEN      10/27/2008

46 MONTH06    4/23/2009

46 FLOW          10/27/2008

46 MONTH12    10/26/2009

46 FLOW            4/23/2009

46 FLOW            12/3/2009

46 MONTH18     6/10/2010

46 FLOW            1/6/2011

46 MONTH24     1/6/2011

53 SCREEN       12/19/2008

53 MONTH06     1/26/2009

;

data want;

     length visit_date_ren $ 10;

     retain _s;

     set have;

     if b= 'SCREEN' then

           _s=visit_date;

     if anydigit(b)>0 then

           do;

                if visit_date > intnx('month',_s,compress(b,,'kd')+3,'s') then

                     visit_date_ren='above';

                else if visit_date < intnx('month',_s,compress(b,,'kd')-3,'s') then

                     visit_date_ren='below';

                else visit_date_ren=put(visit_date,mmddyy10.);

           end;

     drop _s;

run;

Regards,

Haikuo

Frequent Contributor
Posts: 82

Re: Define exact time period

Dear All,

Thanks so much for your efforts and kindness. I tried some programs, the results were not I expected. Here are the details of my project.

I have 80 subjects to monitor lab examinations. Each subject have screen visit as baseline, then has  4 follow ups at 6, 12, 18, and 24 months.

I will use month 6, 12, 18, and 24 to do survival analysis. The follow up visit date maybe before or after designed visit date (or called target date).

So I want to find out If the real visit date is +/- 3 month of each month. Then I have to resign the follow up months. I would have a new variable to identify with visit date is out of the range target date.

Here is my data:

ID   B                  visit_date
46 SCREEN      10/27/2008
46 MONTH06    4/23/2009
46 FLOW          10/27/2008
46 MONTH12    10/26/2009
46 FLOW            4/23/2009
46 FLOW            12/3/2009
46 MONTH18     6/10/2010
46 FLOW            1/6/2011
46 MONTH24     1/6/2011
53 SCREEN       12/19/2008

53 MONTH06     1/26/2009

Thanks,

Regular Contributor
Posts: 233

Re: Define exact time period

Can you provide us what is the output you are looking for just like the data what you have currently?  That way its easier, faster and efficient.

Frequent Contributor
Posts: 82

Re: Define exact time period


Hi Hima,

Yes, just add a new available to show status of visit_date.

Trusted Advisor
Posts: 1,131

Re: Define exact time period

could you please try the below code and let me know if you are getting the expected output

data have;

  input ID   B $                 visit_date: mmddyy10.;

  format visit_date date9.;

cards;

46 SCREEN      10/27/2008

46 MONTH06    4/23/2009

46 FLOW          10/27/2008

46 MONTH12    10/26/2009

46 FLOW            4/23/2009

46 FLOW            12/3/2009

46 MONTH18     6/10/2010

46 FLOW            1/6/2011

46 MONTH24     1/6/2011

53 SCREEN       12/19/2008

53 MONTH06     1/26/2009

;

run;

proc sort data=have;

by id;

run;

data want;

  set have;

  by id;

  retain new;

  if first.id then new=visit_date;

  dif=intck('month',new,visit_date);

if b in ('MONTH06','MONTH12','MONTH18','MONTH24') then do;

if -3 <= dif < 0 then B='Below';

else if 0 < dif <=3 then B='Above';

end;

  format new date9.;

run;

Thanks,

Jag

Thanks,
Jag
Regular Contributor
Posts: 233

Re: Define exact time period

DATA WANT (DROP=LAG_DATE DIFF);

SET HAVE;

FORMAT LAG_DATE MMDDYY10.;

IF INDEX(B,'MONTH') GE 1 THEN DO;

LAG_DATE=LAG(visit_date);

DIFF=INTCK('MONTH',LAG_DATE,visit_date,'C');

IF DIFF NOT IN (3) THEN FLAG='ABOVE';

END;

ELSE DO;

FLAG='BELOW';

END;

RUN;


Capture.JPG
Trusted Advisor
Posts: 1,204

Re: Define exact time period

What do you mean by range within each id? It is calculated as max(date)-min(date)  within each id.

Super User
Posts: 9,687

Re: Define exact time period

data have;
     input
           ID   B :$10. visit_date :mmddyy10.;
     format  visit_date mmddyy10.;
     cards;
46 SCREEN      10/27/2008
46 MONTH06    4/23/2009
46 FLOW          10/27/2008
46 MONTH12    10/26/2009
46 FLOW            4/23/2009
46 FLOW            12/3/2009
46 MONTH18     6/10/2010
46 FLOW            1/6/2011
46 MONTH24     1/6/2011
53 SCREEN       12/19/2008
53 MONTH06     1/26/2009
;
run;
data want;
 merge have have(where=(b='SCREEN') rename=(visit_date=_date));
 by id;
 if b eq: 'MONTH' then do;
   if visit_date gt intnx('month',_date,3,'s') then flag='ABOVE';
    else if visit_date lt intnx('month',_date,-3,'s') then flag='BELOW';
end;
run;

Xia Keshan

Frequent Contributor
Posts: 82

Re: Define exact time period

Hi Ksharp,

I used your program to get  output below:

146SCREENING10/27/2008 flag
846MONTH0604/23/2009ABOVE
1546MONTH1210/26/2009ABOVE
1846MONTH1806/10/2010ABOVE
2046MONTH2401/06/2011ABOVE
2153SCREENING12/19/2008
2853MONTH0606/25/2009ABOVE

3 month, the flag should not show above. Should I make some changes for the program?

Thanks,

Xinjian

Super User
Posts: 9,687

Re: Define exact time period

?? What do you mean ? Should be Below ?

data want;

merge have have(where=(b='SCREEN') rename=(visit_date=_date));

by id;

if b eq: 'MONTH' then do;   

if visit_date gt intnx('month',_date,3,'s') then flag='BELOW';  

  else if visit_date lt intnx('month',_date,-3,'s') then flag='ABOVE';

end;

run;

Xia Keshan

Frequent Contributor
Posts: 82

Re: Define exact time period

when you compare screen and month06 for ID 46, visit_date is in the range +/- 3 month of month06. We should not call these above or below. That is why I ask some changes.

Thanks,

Xinjian

Trusted Advisor
Posts: 1,204

Re: Define exact time period

data want(drop=_date);

set have;

by id;

retain _date;

format _date mmddyy10.;

if first.id then _date=visit_date;

     if b eq: 'MONTH' then do;

     if intck('month',_date,visit_date)- input(compress(b,,'kd'),8.)>3 then flag='Above';

  else if intck('month',_date,visit_date)- input(compress(b,,'kd'),8.)<-3 then flag='Below';

end;

run;

Frequent Contributor
Posts: 82

Re: Define exact time period


Hi Everyone,

Input from each of you give me lot of help. I just find out I have to use consent_ date to determine if visit_date of month06, 12,18, and 24  are out of +/- 3 month range.

I need your further help.

Jag and stat@sas's way are more fit for me.

here is the data:

ID    B          visit_date   consent_date

46    SCREENING   27-Oct-08   27-Oct-08

46    MONTH06     23-Apr-09        .

46    NOVA        27-Oct-08        .

46    MONTH12     26-Oct-09        .

46    NOVA        23-Apr-09        .

46    NOVA        3-Dec-09         .

46    MONTH18     10-Jun-10        .

46    NOVA        6-Jan-11         .

46    MONTH24     6-Jan-11         .

53    SCREENING   19-Dec-08   22-Dec-08

53    MONTH06     25-Jun-09        .

Here is the output I want:

ID    B         visit_date     dif from consent_date     flag

46    SCREEN      27-Oct-08   0    

46    MONTH06     23-Apr-09   6    

46    FLOW        27-Oct-08   0    

46    MONTH12     26-Oct-09   12   

46    FLOW        23-Apr-09   6    

46    FLOW        3-Dec-09    14   

46    MONTH18     10-Jun-10   20   

46    FLOW         6-Jan-11   27   

46    MONTH24     6-Jan-11    27   

53    SCREEN      19-Dec-08   0    

53    MONTH06     26-Jan-09   1                        Below

Thanks,

Xinjian

Trusted Advisor
Posts: 1,204

Re: Define exact time period

data want(drop=_date consent_date _consent_date);

set have;

by id;

retain _date _consent_date;

if first.id then do;

     _date=visit_date;

  _consent_date=consent_date;

end;

dif_from_consent_date=intck('month',_consent_date,visit_date);

     if b eq: 'MONTH' then do;

     if intck('month',_date,visit_date)- input(compress(b,,'kd'),8.)>3 then flag='Above';

  else if intck('month',_date,visit_date)- input(compress(b,,'kd'),8.)<-3 then flag='Below';

end;

run;

Ask a Question
Discussion stats
  • 15 replies
  • 437 views
  • 0 likes
  • 6 in conversation