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?
The syntax is different for fedSQL. Sorry about the confusion.
I got it by doing and put(datepart(Effectivedate), date9.)='01MAY2019')
Worked just fine.
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.
Not using a function would speed up your query. Like this:
where effectiveDate = "01may2019:00:00:00"dt
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.
I tried adding "d" after the string. It does not like it. It says "syntax error" near "D"
The syntax is different for fedSQL. Sorry about the confusion.
Still compare to a datetime though, rather than converting to a date.
Why is this so well hidden? Really hard for current SAS programmers when there is a syntax change like this.
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.
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 25. 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.