topic Re: max and min of date fields in SAS Programming

max and min of date fields

SASAna — Wed, 21 Apr 2021 14:48:36 GMT

Hi SAS Users,

I am working on substracting max(end_date) - min(begin_date) on one code. end_Date and Begin_date fields are datetime fields. when i add max and min to those fields they are converting to numbers.

I am looking to get the total number of days from that substraction.

Total_days = max(end_Date) - min (begin_date)

Thanks,

Ana

Re: max and min of date fields

NicoM — Wed, 21 Apr 2021 15:07:49 GMT

The reason that the result of your formula is numeric, is that in SAS a date or datetime is in fact numeric with a SAS format applied to it. If you remove the format SAS stores a date as the number of days since 1 January 1960 and a datetime as the number of seconds since 1 January 1960.

If you are purely interested in the difference in days between two days, you can subtract them. Since you are working with datetimes, you will get the difference in seconds. I would recommend to convert the datetimes to dates first and then subtracting them. You can do this using the datepart function.

data want;
	begin_dttm = '05jan2020:00:00:00'dt;
	end_dttm = datetime(); 

	begin_date = datepart(begin_dttm); 
	end_date = datepart(end_dttm); 

	days_diff = end_date - begin_date; 
	seconds_diff = end_dttm - begin_dttm; 

	format 
		begin_dttm end_dttm datetime20.2
		begin_date end_date date9.
	;
run;

This will result in the following:

By the way, how are you using the min and the max function? Is this within a data step or in an SQL query?

Re: max and min of date fields

PaigeMiller — Wed, 21 Apr 2021 15:08:36 GMT

It they are datetime values, then because SAS expresses internally the datetimes as the number of seconds since 01JAN1960:00:00:00 when you do a subtraction, you get the difference in seconds.

Since there are 24 hours a day and 60 minutes in an hour and 60 seconds in an hour, then you take the difference (which as I said is in seconds) and divide by 24*60*60 to get the number of days. You can choose to round off to integer days, if you wish.

Re: max and min of date fields

ballardw — Wed, 21 Apr 2021 15:10:06 GMT

Max of what? What you show is the maximum of a single value of end_date so that doesn't really make much sense. Same with your minimum.

Of course the result is a number , you are doing subtraction. You just likely haven't paid attention to the bit about SAS DATETIME values are measure in seconds. So the subtraction you are doing is giving you the number of seconds between the two values.

If you are looking for the number of days between two values and your values are actually datetime values then

Total_days = intck('dtday',begin_date,end_date);

may be what you are looking for. INTCK is the function to return intervals between date, datetime or time values. the "DTDAY" tells SAS the expected values are datetime, the DT part and you want DAY as the interval returned. You could ask for "dtmonth" to get the months between or "dtyear" for years.

Otherwise Provide some example data and what the result should look like.

https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/ta-p/424354 has a PDF with much information about dates.

Re: max and min of date fields

PaigeMiller — Wed, 21 Apr 2021 15:14:21 GMT

Also, you can find the number of days difference as follows

data want;
	begin_dttm = '05jan2020:00:00:00'dt;
	end_dttm = datetime(); 
    diff=intnx('dtday',begin_dttm,end_dttm);
run;

Re: max and min of date fields

maguiremq — Wed, 21 Apr 2021 15:15:25 GMT

Are there multiple dates for a given person? If so, you can do it in a single PROC SQL statement. Note that it is repetitive just to show you the steps.

data have;
input id begin_date :datetime. end_date :datetime.;
format begin_date end_date datetime.;
datalines;
1 21APR2021:11:00:00 24APR2021:11:00:00
1 31MAR2021:11:00:00 12APR2021:11:00:00
2 14FEB2021:09:00:00 15NOV2021:04:00:00
;
run;

proc sql;
	select
				distinct id,
				min(datepart(begin_date)) as min_begin_date
					format = mmddyy10.,
				max(datepart(end_date)) as max_end_date
					format = mmddyy10.,
				intck("days", min(datepart(begin_date)), max(datepart(end_date)), "c") as total_days
	from
				have
	group by
				id;
quit;

id min_begin_date max_end_date total_days 
1 03/31/2021 04/24/2021 24 
2 02/14/2021 11/15/2021 274

I also assumed you meant an actual datetime format and not just a date. If you don't have a datetime format, you don't need to use the DATEPART function.

See here for more about the INTCK function: https://blogs.sas.com/content/iml/2017/05/15/intck-intnx-intervals-sas.html

Re: max and min of date fields

Reeza — Wed, 21 Apr 2021 15:32:10 GMT

Did you mean to use INTCK?

Re: max and min of date fields

SASAna — Wed, 28 Apr 2021 23:28:53 GMT

Thanks much. It worked very well.