topic Re: FedSQL and Passing Dates in Filter in SAS Programming

FedSQL and Passing Dates in Filter

Santha — Tue, 03 Mar 2020 21:15:56 GMT

I am working with SAS Viya.

Here is my code

cas;
caslib _all_ Assign;

proc fedsql sessref=casauto;
create table PN_Transit as
(select distinct Port1 from LM.TRANS
where MasterClientName = 'D'
and datepart(EffectiveDate) ='01MAY2019');
quit;

LM.TRANS is a table in SQL that is connected. This query works just fine if I do not have the date filter. It works for any other filter that I want to have except the date one. So I am not sure if this is the right syntax. This does create a table called PN_Transit like I wanted it to but i returns 0 observations. I tried several combinations in the date filter but in vain. In the SQL table, the effective date is actually stored as "2019-05-01 00:00:00.000". How do I get this working?

Re: FedSQL and Passing Dates in Filter

Santha — Tue, 03 Mar 2020 21:41:30 GMT

I got it by doing and put(datepart(Effectivedate), date9.)='01MAY2019')

Worked just fine.

Re: FedSQL and Passing Dates in Filter

Reeza — Tue, 03 Mar 2020 21:54:15 GMT

datepart(effectiveDate) = '01May2019'd

The above should work as well.

@Santha wrote:

I got it by doing and put(datepart(Effectivedate), date9.)='01MAY2019')

Worked just fine.

Re: FedSQL and Passing Dates in Filter

ChrisNZ — Tue, 03 Mar 2020 21:54:28 GMT

You need to tell SAS this is a date. Add D after the string:

datepart(EffectiveDate) = '01MAY2019'd

Other letters can be added like T for time, DT for datetime, X for hexadecimal, N for name, B for binary.

Re: FedSQL and Passing Dates in Filter

Santha — Tue, 03 Mar 2020 22:02:36 GMT

I tried adding "d" after the string. It does not like it. It says "syntax error" near "D"

Re: FedSQL and Passing Dates in Filter

Reeza — Tue, 03 Mar 2020 22:03:24 GMT

With your date in that format? Can you post the code/log?

Re: FedSQL and Passing Dates in Filter

Santha — Tue, 03 Mar 2020 22:10:02 GMT

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

76 proc fedsql sessref=casauto;

77 create table PN_Transit as

78 (select distinct Port1 from LM.TRANS

79 where MasterClientName = 'D'

80 and datepart(EffectiveDate) ='01MAY2019'd);

ERROR: Syntax error at or near "D"

ERROR: The action stopped due to errors.

ERROR: The FedSQL action was not successful.

NOTE: PROC FEDSQL has set option NOEXEC and will continue to prepare statements.

81 quit;

NOTE: PROCEDURE FEDSQL used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

83 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

Re: FedSQL and Passing Dates in Filter

ChrisNZ — Tue, 03 Mar 2020 22:10:31 GMT

Not using a function would speed up your query. Like this:

where effectiveDate = "01may2019:00:00:00"dt

Re: FedSQL and Passing Dates in Filter

ChrisNZ — Tue, 03 Mar 2020 22:12:06 GMT

The syntax is different for fedSQL. Sorry about the confusion.

See here: https://documentation.sas.com/?docsetId=fedsqlref&docsetTarget=n0250hv7mns19hn18gi0clo71j7e.htm&docsetVersion=9.4&locale=en

Re: FedSQL and Passing Dates in Filter

ChrisNZ — Tue, 03 Mar 2020 22:13:20 GMT

Still compare to a datetime though, rather than converting to a date.

Re: FedSQL and Passing Dates in Filter

PaalNavestad — Fri, 26 Mar 2021 15:03:41 GMT

Why is this so well hidden? Really hard for current SAS programmers when there is a syntax change like this.

Re: FedSQL and Passing Dates in Filter

Reeza — Fri, 26 Mar 2021 15:10:54 GMT

How is something that has it's own chapter in the documentation "hidden"?

FEDSQL is essentially another language entirely, like HASH table coding for example.

Re: FedSQL and Passing Dates in Filter

PaalNavestad — Fri, 19 Nov 2021 09:34:11 GMT

Hi the documentation explain the way dates and time is handled. I had a challenge understanding this.
The way to use a date in a where clause is

where dateVariable >= date'2015-01-01'
a bit confusing as you tell what it is in front and that there are no () before the date constant.
Fortunatly the date() function worked well in subsets.

Re: FedSQL and Passing Dates in Filter

PaalNavestad — Fri, 19 Nov 2021 11:54:42 GMT

Hidden is mainly the wrong word. The challenge is that it is rather different from the SQL procedure so an article, or change type log would have been good.