BookmarkSubscribeRSS Feed
xinjian
Calcite | Level 5

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.

15 REPLIES 15
Haikuo
Onyx | Level 15

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

xinjian
Calcite | Level 5

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,

Hima
Obsidian | Level 7

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.

xinjian
Calcite | Level 5


Hi Hima,

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

Jagadishkatam
Amethyst | Level 16

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
Hima
Obsidian | Level 7

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
stat_sas
Ammonite | Level 13

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

Ksharp
Super User
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

xinjian
Calcite | Level 5

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

Ksharp
Super User

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

xinjian
Calcite | Level 5

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

stat_sas
Ammonite | Level 13

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;

xinjian
Calcite | Level 5


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

stat_sas
Ammonite | Level 13

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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