adding weeks to the max of the date and comparing it with datetimefield

[SASAna]

Hi SAS Users,

Need a help solving the dates correctly. 

I am looking for the members where the datetime1 is 2 weeks greater than max_datetime2.

b.datetime1 -  Ex: 06FEB2021:11:37:16.453000   ( it is in date time format)

a.a.max_datetime2 - it is max of some datetime field . ( It is in numeric format)  - Ex: 1925365763.1

My Query - 

proc sql;
create table two_weeks as 
select a.memberid, a.callid,count(*)
from
table1 a inner join table2 b
on a.memberid = b.memberid
and b.datetime1 >= (a.max_datetime2 + 2 weeks )
group by a.memberid, a.callid
;
Quit;

Thanks,

Ana

[ballardw]

You might want to provide a variety of actual values to compare. Since you have datetime values you need to consider how the TIME component affects your comparison.

Consider if I have a datetime that is noon (12:00:00 time component). If I compare to datatime the next day is it "one day" if the time is only 11:30:00? For some purposes it may, for others it may not.

You can get the intervals between two dates, times or datetimes by using the INTCK function. The function takes as the first parameter a description of the interval of interest. For datetime values the common intervals would be 'dtday' 'dtweek' 'dtmonth' 'dtyear' 'hour' 'minute' 'second'. Some examples with the 'dtweek'

data example;
   dt1 = '06FEB2021:11:37:16'dt;
   dt2 = '13Feb2021:10:00:00'dt;
   dt3 = '13Feb2021:12:00:00'dt;
   /* continuous considers the time interval differently
      since the second time is not the same or later than
      the first it is not a complete week
   */
   dt1_dt2_c = intck('dtweek',dt1, dt2,'continuous');
   dt1_dt2_d = intck('dtweek',dt1, dt2,'discrete');
   dt1_dt3_c = intck('dtweek',dt1, dt3,'c');
   dt1_dt3_d = intck('dtweek',dt1, dt3,'d');
   /* discrete is the default for the last parameter if not provided*/
run;

The DT tells SAS the values are expected to be datetimes and not dates which use 'week' 'month' 'day' 'year'.

So you use the function to return the number of intervals directly such as

and intck('dtweek',a.max_datetime2, b.datetime1,'d') ge 2 

( if I parsed your code correctly)

for example. Use the appropriate 'd' or 'c' for YOUR purpose.

There is another function INTNX to increment dates, times and datetime values

[Reeza · Accepted solution]

b.datetime1 >= intnx('dtday', a.max_datetime2, 14, 's' )

Try INTNX and DTDAY as the interval, which is 14 days. 

---

@SASAna wrote:

Hi SAS Users,

Need a help solving the dates correctly. 

I am looking for the members where the datetime1 is 2 weeks greater than max_datetime2.

b.datetime1 -  Ex: 06FEB2021:11:37:16.453000   ( it is in date time format)

a.a.max_datetime2 - it is max of some datetime field . ( It is in numeric format)  - Ex: 1925365763.1

My Query - 

proc sql;
create table two_weeks as 
select a.memberid, a.callid,count(*)
from
table1 a inner join table2 b
on a.memberid = b.memberid
and b.datetime1 >= (a.max_datetime2 + 2 weeks )
group by a.memberid, a.callid
;
Quit;

Thanks,

Ana

---

[SASAna]

Thank you. I am trying to see the max_datetime2 field in YYMMDDn8 format and add 14 days to test the above solution.Date3 is resolving to blank answer.

Data test;
date3 = input(put(1925365763.1,yymmddn8.),8.);
run;

[Tom]

---

@SASAna wrote:
Thank you. I am trying to see the max_datetime2 field in YYMMDDn8 format and add 14 days to test the above solution.Date3 is resolving to blank answer.

Data test;
date3 = input(put(1925365763.1,yymmddn8.),8.);
run;

---

This does not make much sense at all.  Let's look at what that those function calls are doing.

First you are trying to use the PUT() to convert the number 1,925,365,763.1 into a string using the YYMMDDN8. format.  If we divide that giant number by the number of days in a year and add it to the base year that SAS uses for day number zero we get a year that would require way more than 4 digits to represent.

806   data test;
807     days = 1925365763 ;
808     years = 1960 + int(days/365.25) ;
809     put (_all_) (=comma15.);
810   run;

days=1,925,365,763 years=5,273,324

Now lets look at the other function call does. 

First we assume that you actually generated string in YYYYMMDD style, for example day's date in that style would be the string "20210428".  So if you convert that string to a number by reading the first 8 bytes then you would get the number 20,210,428. 

What are you going to do with this number?

And why would you assign it to a variable with a name like DATE3?

Perhaps the number 1,925,365,763.1  was really a datetime value (number of seconds since 1960)?  Lets see what it looks like when formatted as a datetime value.

811   data test;
812     seconds  = 1925365763 ;
813     put seconds = comma15. '-> ' seconds datetime20. ;
814   run;

seconds=1,925,365,763 ->   04JAN2021:07:49:23

So that is some time on the morning of January 4th of this year.

If you want to convert that to a date you can use the DATEPART() function.

815   data test;
816     seconds  = 1925365763 ;
817     days = datepart(seconds);
818     put seconds = comma15. '-> ' seconds datetime20. ;
819     put days = comma15. '-> ' days date9. ;
820   run;

seconds=1,925,365,763 ->   04JAN2021:07:49:23
days=22,284 -> 04JAN2021

If you want the date to print in that YYYYMMDD style then use the YYMMDDN8. format instead of the DATE9. format.

And if you really did want to store the number 20,210,104 into a numeric variable like original input(put()) function call appeared to want to do you could use:

827   data test;
828     seconds  = 1925365763 ;
829     date_integer = input(put(datepart(seconds),yymmddn8.),8.);
830     put seconds = comma15. '-> ' seconds datetime20. ;
831     put date_integer = '-> ' date_integer comma15.;
832   run;

seconds=1,925,365,763 ->   04JAN2021:07:49:23
date_integer=20210104 ->      20,210,104

[SASAna]

Thanks for the explanation. It helped me for testing.

[SASAna]

Thanks Reeza, it worked well.