Desktop productivity for business analysts and programmers

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

Reply
Regular Contributor
Posts: 170

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

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;

Community Manager
Posts: 3,463

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

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.

Super User
Posts: 13,941

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

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.

Super User
Posts: 10,589

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 170

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

Posted in reply to KurtBremser

Hello Kurt,

 

could you provide me an example how to do that?

Super User
Posts: 10,589

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 170

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

Posted in reply to KurtBremser
OK
Community Manager
Posts: 3,463

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

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;
Regular Contributor
Posts: 170

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

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;

Ask a Question
Discussion stats
  • 8 replies
  • 1978 views
  • 2 likes
  • 4 in conversation