## Define exact time period

Frequent Contributor
Posts: 87

# 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.

Posts: 3,167

## 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: 87

## 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: 87

## Re: Define exact time period

Hi Hima,

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

Posts: 1,147

## 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;

Posts: 1,270

## 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: 10,784

## 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: 87

## Re: Define exact time period

Hi Ksharp,

I used your program to get  output below:

 1 8 15 18 46 SCREENING 10/27/2008 flag 46 MONTH06 04/23/2009 ABOVE 46 MONTH12 10/26/2009 ABOVE 46 MONTH18 06/10/2010 ABOVE 46 MONTH24 01/06/2011 ABOVE 53 SCREENING 12/19/2008 53 MONTH06 06/25/2009 ABOVE

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

Thanks,

Xinjian

Super User
Posts: 10,784

## 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: 87

## 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

Posts: 1,270

## 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: 87

## 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.

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

Posts: 1,270

## 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;

Discussion stats
• 15 replies
• 459 views
• 0 likes
• 6 in conversation