topic Re: Define exact time period in SAS Programming

Define exact time period

xinjian — Fri, 05 Sep 2014 15:40:56 GMT

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.

Re: Define exact time period

Haikuo — Fri, 05 Sep 2014 16:02:20 GMT

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

Re: Define exact time period

Jagadishkatam — Fri, 05 Sep 2014 16:16:19 GMT

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

Re: Define exact time period

Hima — Fri, 05 Sep 2014 16:24:39 GMT

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;

Re: Define exact time period

stat_sas — Fri, 05 Sep 2014 16:36:30 GMT

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

Re: Define exact time period

xinjian — Fri, 05 Sep 2014 19:37:25 GMT

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:

53 MONTH06 1/26/2009

Thanks,

Re: Define exact time period

Hima — Fri, 05 Sep 2014 19:43:03 GMT

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.

Re: Define exact time period

xinjian — Fri, 05 Sep 2014 19:46:29 GMT

Hi Hima,

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

Re: Define exact time period

Ksharp — Sat, 06 Sep 2014 13:10:20 GMT

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

Re: Define exact time period

xinjian — Sun, 07 Sep 2014 14:23:16 GMT

Hi Ksharp,

I used your program to get output below:

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

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

Thanks,

Xinjian

Re: Define exact time period

Ksharp — Mon, 08 Sep 2014 12:15:14 GMT

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

Re: Define exact time period

xinjian — Mon, 08 Sep 2014 17:04:08 GMT

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

Re: Define exact time period

stat_sas — Mon, 08 Sep 2014 18:04:14 GMT

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;

Re: Define exact time period

xinjian — Mon, 08 Sep 2014 19:45:37 GMT

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

Re: Define exact time period

stat_sas — Mon, 08 Sep 2014 20:44:12 GMT

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;

Re: Define exact time period

Ksharp — Tue, 09 Sep 2014 12:10:37 GMT

You mean in the range of +/- 3 month , NOT out of the range ?

data want;
merge have have(where=(b='SCREEN') rename=(visit_date=_date)); 
by id; 
if b eq: 'MONTH' then do;    
if intnx('month',_date,-3,'s') le visit_date le intnx('month',_date,3,'s') then do;
  flag=ifc(visit_date gt _date ,'BELOW','ABOVE'); 
end; 
end;
run;

Xia Keshan