Hi,
I have data that look like this. The main group is "id" each of which has a set of dates that may or may not repeat.
id date
1 1/1/2001
1 1/1/2001
1 1/2/2002
1 1/2/2002
1 1/2/2003
1 1/2/2003
2 1/1/2005
2 1/1/2005
2 1/1/2005
2 1/2/2005
2 1/2/2005
2 1/3/2006
2 1/4/2006
for id = 2, note that 1/1/2005 and 1/2/2005 are one month apart, so are 1/3/2006 and 1/4/2006. How can I can SAS to tell me if the dates for a given id are a fixed interval apart from the previous date that is different?
Can the potential solution be tweaked to modify the desired interval and also raise a flag if that interval is observed (one month in this example.)
NOTE: Consider dates in the DD/MM/YYYY format for this example.
Thank you!
Please try
data have;
input id date :ddmmyy10.;
format date date9.;
cards;
1 1/1/2001
1 1/1/2001
1 1/2/2002
1 1/2/2002
1 1/2/2003
1 1/2/2003
2 1/1/2005
2 1/1/2005
2 1/1/2005
2 1/2/2005
2 1/2/2005
2 1/3/2006
2 1/4/2006
;
proc sort data=have;
by id date;
run;
data want;
set have;
by id date;
retain new_date;
new_date=lag(date);
if nmiss(new_date, date)=0 then flag=new_date ne date;
format new_date date9.;
if first.id then do ;
new_date=.;
flag=.;
end;
run;
Thanks,
Jagadish
It would help if you provide what you want your output file to look like given your example data.
id date interval1month
1 1/1/2001 0
1 1/1/2001 0
1 1/2/2002 0
1 1/2/2002 0
1 1/2/2003 0
1 1/2/2003 0
2 1/1/2005 0
2 1/1/2005 0
2 1/1/2005 0
2 1/2/2005 1
2 1/2/2005 0
2 1/3/2006 0
2 1/4/2006 1
Here, the third variable tells us if the interval between the date on that row and the immediately preceding date on the previous row was one month (1) or not (0), thus an indicator.
Alternatively, a more generic solution could be
id date period
1 1/1/2001 0
1 1/1/2001 0
1 1/2/2002 365
1 1/2/2002 0
1 1/2/2003 365
1 1/2/2003 0
2 1/1/2005 695
2 1/1/2005 0
2 1/1/2005 0
2 1/2/2005 30
2 1/2/2005 0
2 1/3/2006 395
2 1/4/2006 30
where a variable simply counts the interval between a given date and the previous date in the preceding row.
Thanks.
Please try
data have;
input id date :ddmmyy10.;
format date date9.;
cards;
1 1/1/2001
1 1/1/2001
1 1/2/2002
1 1/2/2002
1 1/2/2003
1 1/2/2003
2 1/1/2005
2 1/1/2005
2 1/1/2005
2 1/2/2005
2 1/2/2005
2 1/3/2006
2 1/4/2006
;
proc sort data=have;
by id date;
run;
data want;
set have;
by id date;
retain new_date;
new_date=lag(date);
if nmiss(new_date, date)=0 then flag=new_date ne date;
format new_date date9.;
if first.id then do ;
new_date=.;
flag=.;
end;
run;
Thanks,
Jagadish
Hi Jagadish,
Thank you for the suggestion.
It works but only tells me when the date for a given id. It does not give me any control over what interval to choose. I want to know if the date changed and also if the date was for example, one week apart from the previous date. How do I raise a flag for that?
Can we make the table look like this?
id date period
1 1/1/2001 0
1 1/1/2001 0
1 1/2/2002 395
1 1/2/2002 0
1 1/2/2003 365
1 1/2/2003 0
2 1/1/2005 695
2 1/1/2005 0
2 1/1/2005 0
2 1/2/2005 30
2 1/2/2005 0
2 1/3/2006 395
2 1/4/2006 30
the third column is days of difference between a given date and the previous date.
Hi Saurabh,
I modified my code as per your requirement, please check
data want;
set have;
by id date;
retain new_date;
new_date=lag(date);
if nmiss(new_date, date)=0 then period=intck('day',new_date,date);
format new_date date9.;
run;
Thanks,
Jagadish
Are you looking to obtain the three measures computed with the following code?:
data intervals;
set have;
by id;
retain last_date;
interval1month=0;
months=0;
days=0;
if not first.id then do;
months=intck('months',last_date,date);
if months eq 1 then interval1month=1;
days=date-last_date;
end;
last_date=date;
drop last_date;
run;
Hi sourabh
How about this?
data two;
set have;
startdate=lag(date);
if startdate=. then startdate=date;
FORMAT startdate DATE9.;
run;
data want;
set two;
period=date-startdate;
drop startdate;
run;
Hi Jagadish,
I could use your code by changing the end to
if first.id then do ;
new_date=startdate;
flag=0;
end;
I could do with just knowing what the difference between each date and the date of the previous row was. I later created indicators for the desired difference (1month, 1week etc.)
Thanks!
Hi Mit,
Your code essentially gave the same result. Thanks.
Hi Arthur,
Your code will be super useful when the intervals are fixed. I am not sure how the intck function may be used when we might have a variable interval of choice.
Thanks!
Hi Arthur,
Your code will be super useful when the intervals are fixed. I am not sure how the intck function may be used when we might have a variable interval of choice.
Thanks!
make the "interval" a parameter/prompt/macroVar and ensure it is selected from a valid list of intervals (see the doc about intervals in the INTCK() function)
Hi Arthur!
Thanks very much for the suggestions.
Best,
Saurabh
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.