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;
Be aware, using syntax like you just suggested
where datepart(input(a.actual_date),yymmdd10.) >= '13jun2020'd;
Would cause bad performance!
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
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;
Be aware, using syntax like you just suggested
where datepart(input(a.actual_date),yymmdd10.) >= '13jun2020'd;
Would cause bad performance!
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
Ah Thank you for that alert. Much appreciated! Cheers!
Thank you! This worked perfectly and with better performance!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: