BookmarkSubscribeRSS Feed
acordes
Rhodochrosite | Level 12

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;
2 REPLIES 2
JosvanderVelden
SAS Super FREQ
Try it with something like: "put(_fec_unic, year.) = '2023'" instead of "year(cats('date', put(_fec_unic, yymmdd10.))) = 2023".
Tom
Super User Tom
Super User

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))

 

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 600 views
  • 1 like
  • 3 in conversation