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.
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
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,
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.
Hi Hima,
Yes, just add a new available to show status of visit_date.
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
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;
What do you mean by range within each id? It is calculated as max(date)-min(date) within each id.
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
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
?? 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
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
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;
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.