topic Re: proc sql create a table where and year(datepart(datetime)) in SAS Enterprise Guide

proc sql create a table where and year(datepart(datetime))

alepage — Fri, 01 Dec 2017 15:03:57 GMT

Good morning,

I would like to know if it is possible to had : and year(datepart(datetime)) gt 2016 in a where statement of proc sql create table test as

Here's my test code:

This code is working:

proc sql;
create table TableToday as
select *, YEAR(DATEPART(DATETIME)) AS Year
from SASHELP.GNGSMP2
where ID in ('A' , 'B') AND Calculated Year ge 2000;
quit;

The following below is not working. Does anyone could help me with that issue?

libname PRO Meta Library="PRO" METAOUT=DATA;
options nosymbolgen;
proc sql;
CONNECT TO ORACLE(AUTHDOMAIN="PRO" PATH="BI");
create table Table1 as
select primaryentityid , primaryentityclass, started, performerid, 1 as Web,
year(datepart(started)) as Year from connection to ORACLE
(
select primaryentityid, started, performerid, primaryentityclass
from activity a
where (category in ( 'WEB') and Calculated Year ge 2017)

union all
select primaryentityid, started, performerid, primaryentityclass
from activity_history ah
where (category in ('WEB')and Calculated year ge 2017)

);
DISCONNECT FROM ORACLE;
quit;

Re: proc sql create a table where and year(datepart(datetime))

ChrisHemedinger — Fri, 01 Dec 2017 15:20:53 GMT

DATEPART is a SAS function and Oracle can't process that. When using explicit passthrough (CONNECT statement), use an Oracle function.

I think YEAR() works with datetime values -- so YEAR(started), or EXTRACT(YEAR from Started). This from Googling for Oracle doc -- I haven't tested.

Re: proc sql create a table where and year(datepart(datetime))

ballardw — Fri, 01 Dec 2017 15:36:36 GMT

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of a dataset, the actual results and the expected results. Data should be in the form of a data step. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: proc sql create a table where and year(datepart(datetime))

Kurt_Bremser — Fri, 01 Dec 2017 16:19:28 GMT

You are creating year locally in SAS, but try to use it in the pass-through to Oracle. Create it (using Oracle functions) in the pass-through.

Re: proc sql create a table where and year(datepart(datetime))

alepage — Fri, 01 Dec 2017 18:09:48 GMT

Hello Kurt,

could you provide me an example how to do that?

Re: proc sql create a table where and year(datepart(datetime))

Kurt_Bremser — Fri, 01 Dec 2017 18:32:19 GMT

I'm no Oracle expert, so you best ask your Oracle specialists.

Re: proc sql create a table where and year(datepart(datetime))

ChrisHemedinger — Fri, 01 Dec 2017 18:49:39 GMT

Here's your code reworked in a way that (I hope) should work for Oracle. I changed just the date functions -- not anything about your join logic. I'm not conversant in Oracle SQL nuances.

libname PRO Meta Library="PRO" METAOUT=DATA;
options nosymbolgen;
proc sql;
CONNECT TO ORACLE(AUTHDOMAIN="PRO" PATH="BI");
create table Table1 as
select primaryentityid , primaryentityclass, started, performerid, 1 as Web,
 year(started) as Year from connection to ORACLE
(
	select primaryentityid, started, performerid, primaryentityclass
	from activity a
	where (category in ( 'WEB') and year(started) ge 2017)
	union all
	select primaryentityid, started, performerid, primaryentityclass
	from activity_history ah
	where (category in ('WEB')and year(started) ge 2017)
);
DISCONNECT FROM ORACLE;
quit;

Re: proc sql create a table where and year(datepart(datetime))

alepage — Wed, 06 Dec 2017 13:32:25 GMT

Good morning,

One of my colleagues found the proper code I was looking for.

Here’s the code:

proc sql;
CONNECT TO ORACLE(AUTHDOMAIN="PRO_REPL" PATH="BI_PROD");
create table SoumEnLigne as
select primaryentityid , primaryentityclass, started, performerid, Year(datepart(started)) as Year, 1 as Web from connection to ORACLE
(
select primaryentityid, started, performerid, primaryentityclass
from activity a
where started >=to_date('2017-01-01', 'yyyy-mm-dd')
)
union all
select primaryentityid, started, performerid, primaryentityclass
from activity_history ah
where started >=to_date('2017-01-01', 'yyyy-mm-dd')
)
);
DISCONNECT FROM ORACLE;
quit;

Re: proc sql create a table where and year(datepart(datetime))

alepage — Wed, 06 Dec 2017 13:33:49 GMT