topic date based where clause in fedsql in SAS Programming

date based where clause in fedsql

acordes — Thu, 20 Jul 2023 08:28:25 GMT

I cannot figure out how to make this work in fedsql.

_fec_unic is numeric with date format.

I cannot use year(_fec_unic)=2023.

If I hard code a date like year(date'2023-03-28')=2023 it works.

But my attempts fail to use it with the date variable.

proc cas;
source MPG_toyota;
			select cod_conc, cod_mrc, sum(num) as uds, sum(denum) as vtas
               from ONAIR.HIERARCHY_GC_COCKPIT_V14 where cod_conc='0311P' and transaction='GC Privado' and
		year(cats('date', put(_fec_unic, yymmdd10.))) = 2023 
				group by cod_conc, cod_mrc ;
endsource;
fedSQL.execDirect / query=MPG_toyota;
quit;

Re: date based where clause in fedsql

JosvanderVelden — Thu, 20 Jul 2023 15:05:36 GMT

Try it with something like: "put(_fec_unic, year.) = '2023'" instead of "year(cats('date', put(_fec_unic, yymmdd10.))) = 2023".

Re: date based where clause in fedsql

Tom — Thu, 20 Jul 2023 15:20:44 GMT

That does not look at all equivalent.

DATE '2023-03-28'

Is a DATE literal in many SQL implementation.

cats('date', put(_fec_unic, yymmdd10.)

Is generating a STRING , which is invalid as the input to the YEAR() function.

If _fec_unic has DATE values in it (which is required for the YYMMDD format to work) then you should be able to just use:

 YEAR(_fec_unic)

If instead it has DATETIME values (ie number of seconds since 1960 instead of number of days) then you will need to also include DATEPART() function to convert the seconds into days.

 YEAR(DATEPART(_fec_unic))