FedSQL and Passing Dates in Filter

[Santha]

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?

[Santha]

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

Worked just fine.

[Reeza]

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.

---

[ChrisNZ]

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

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

[ChrisNZ]

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.

[Santha]

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

[Reeza]

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

[Santha]

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

75

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

82

83 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

[ChrisNZ · Accepted solution]

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

See here: https://documentation.sas.com/?docsetId=fedsqlref&docsetTarget=n0250hv7mns19hn18gi0clo71j7e.htm&docs...

[ChrisNZ]

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

[PaalNavestad]

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

[Reeza]

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.

[PaalNavestad]

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.

[PaalNavestad]

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.