topic Bringing in date from SQL table in SAS Programming

Bringing in date from SQL table

ddavies — Wed, 17 Jun 2020 17:34:04 GMT

When I run the code below, I get this error:

102 WHERE a.actual_date >= '2020-06-13T00:00:00.000';
ERROR: Expression using greater than or equal (>=) has components that are of different data types.

I believe the error is because the format on the raw table is a SQL date format, not a character or numeric variable.

Suggestions?

LIBNAME EVL ODBC DSN=xxx user=xxx pw=xxx schema=dbo; 

proc sql;
	create table billable_hours as
	select a.people_id
	      ,a.group_profile_id
	      ,a.actual_date
	      ,a.completed
	      ,a.end_date
	      ,a.program_providing_service_name
	      ,a.site_providing_service_name
	      ,a.event_name
	      ,a.is_service_event
	      ,a.category_name
	      ,a.generic_description
	      ,a.duration_total
	      ,a.date_entered
	      ,a.do_not_bill
	      ,a.staff_id
	      ,a.is_locked
	      ,a.date_locked
	      ,a.locked_by
	      ,a.approved_by
	      ,a.approved_date
	      ,a.category_code
	      ,a.actual_location_name
	      ,a.is_deleted
	      ,a.is_final
		  ,a.is_e_signed
	      ,a.is_billed
	      ,a.expiration_date
	      ,a.service_provider_name
	      ,a.staff_entered_event
	      ,a.general_location
	      ,a.full_name
	      ,b.activity_type_id
  FROM EVL.event_expanded_view as a
    left join EVL.service_events_view as b
		on a.event_log_id = b.event_log_id
  WHERE a.actual_date >= '2020-06-13T00:00:00.000';
quit;

Re: Bringing in date from SQL table

novinosrin — Wed, 17 Jun 2020 17:43:37 GMT

Hi @ddavies The ODBC engine should enable SAS to manipulate data from a SQL table using SAS language functions just like a SAS dataset right?

If yes, the following should do?

 WHERE datepart(a.actual_date) >= '13jun2020'd;

And if "char"

 where datepart(input(a.actual_date),yymmdd10.) >= '13jun2020'd;

Re: Bringing in date from SQL table

AhmedAl_Attar — Wed, 17 Jun 2020 17:55:55 GMT

@novinosrin

Be aware, using syntax like you just suggested

where datepart(input(a.actual_date),yymmdd10.) >= '13jun2020'd;

Would cause bad performance!

@ddavies

You would be better served using

WHERE a.actual_date >= '13jun2020'd;

The SAS/ACCESS Engine, would translate the statement to the best syntax your DataBase accepts.

Check this paper for confirmation An Insider’s Guide to SAS/ACCESS® Interface to ODBC

Hope this helps,

Ahmed

Re: Bringing in date from SQL table

novinosrin — Wed, 17 Jun 2020 17:58:55 GMT

Ah Thank you for that alert. Much appreciated! Cheers!

Re: Bringing in date from SQL table

ddavies — Wed, 17 Jun 2020 18:00:34 GMT

Thank you! This worked perfectly and with better performance!