topic Re: Proc sql : datepart function in SAS Programming

Proc sql : datepart function

SASdevAnneMarie — Sun, 12 Sep 2021 10:53:20 GMT

Hello Experts,

I would like to get the Year with Datepart function in proc sql, but my code doesn't work.

Could you help me, please :

proc sql;
	create table TEST as 
		select count(distinct(ev.d_effet)) as nombre_versement_15ans from psaeu11.db_evenement ev,
			psaeu11.dp_classe_evt classe
		where ev.no_police = 'AFFGV'
			and classe.b_ea =1  and classe.b_rachat = 1 
			and ev.is_classe_evt=classe.is_classe_evt and 
			year(datepart(y,ev.d_effet))>=year(datepart(y,today()))-15;
quit;

Thank you !

Re: Proc sql : datepart function

PaigeMiller — Sun, 12 Sep 2021 12:56:11 GMT

The DATEPART() function has only one argument. You are using DATEPART() with two arguments, that won't work. DATEPART() also only works on data/time values, do your variables contain date/time values?

Re: Proc sql : datepart function

Tom — Sun, 12 Sep 2021 15:20:05 GMT

The DATEPART() function just converts number of seconds (DATETIME value) into number of days (DATE value). Basically it is just dividing by 24*60*60.

If you want the YEAR from a DATE value you can use the YEAR() function.

So if you want the YEAR from a DATETIME value you can just nest the two function calls.

select YEAR(DATEPART(datetime_var)) as year_var
...

Re: Proc sql : datepart function

SASdevAnneMarie — Sun, 12 Sep 2021 17:08:27 GMT

Thank you, Tom !