topic Re: Error on count in SAS Enterprise Guide

Error on count

deleted_user — Thu, 22 May 2008 21:18:15 GMT

libname ODSP oracle path=odsp schema=xxxxx user=xxxxx password= XXXXXXXXXXX ;

proc sql;
select count(*) from fund_hierarchy;
ERROR: File WORK.FUND_HIERARCHY.DATA does not exist.

I am getting this error. I am interested to know the count of rows in FUND_HIERARCHY. Please help Message was edited by: Vanitha

Re: Error on count

Cynthia_sas — Thu, 22 May 2008 22:47:56 GMT

Hi:
Is FUND_HIERARCHY a temporary dataset or a permanent SAS dataset? Could FUND_HIERARCHY be an Oracle table, perhaps in the Oracle database that you are pointing to in your LIBNAME statement??

If FUND_HIERARCHY is an Oracle table, then you might need to refer to the SAS/Access documentation for Oracle for an example of accessing an Oracle table in your select statement:
http://support.sas.com/documentation/onlinedoc/91pdf/sasdoc_91/access_oracle_7367.pdf (see pages 4, 7 and 11)
or look at these other references:
http://support.sas.com/kb/30/591.html
http://www2.sas.com/proceedings/forum2007/239-2007.pdf

If you are still having issues after consulting the documentation, then your best bet for help is to contact someone at your site who already uses SAS/Access to Oracle (who may have some sample code) or contact SAS Tech Support.
cynthia

Re: Error on count

deleted_user — Fri, 23 May 2008 12:14:02 GMT

you forgot to use the library you defined.
[pre]
... from ODSP.fund_hierarchy;
[/pre]

Re: Error on count

deleted_user — Fri, 23 May 2008 13:47:37 GMT

Thank you very much.
I do have the libname statement to point to the oracle table. My stored process with this libname statement runs fine. I do not know why the following code gives error
libname ODSP oracle path=odsp schema=odsmgr user=ods_view2 password= XXXXXXXXXXX ;
ERROR: The ORACLE engine cannot be found.
ERROR: Error in the LIBNAME statement.
15
16 proc sql;
17 select count(*) from odsp.fund_hierarchy;
ERROR: Libname ODSP is not assigned.

Any other suggestions are greatly appreciated.

Re: Error on count

Cynthia_sas — Fri, 23 May 2008 15:12:22 GMT

Hi:
You did not say in your original post that this was a stored process. Are you sure that the LIBNAME statement is correct for a Stored Process that uses the metadata server? Are your Oracle tables defined in the metadata with the correct engine? Are you sure your Workspace server or Stored Process server can "talk" to the Oracle server?

How did you build your stored process? From an EG project that was working or from an EG project that had errors reading the Oracle file? I believe, that in the Enterprise Intelligence Platform, there also could be some configuration and platform administration issues about which servers "know" about the location of the database tables and therefore, affect, which servers you choose to execute your stored process.

To me, this seems to be a question best answered by SAS Tech Support.

cynthia

Re: Error on count

deleted_user — Mon, 26 May 2008 14:53:04 GMT

Ideas:

1) You don't have SAS/ACCESS for Oracle installed on the host running the code.
2) You don't have the Oracle client installed on the host running the code.
3) Both

Re: Error on count

deleted_user — Tue, 27 May 2008 13:31:21 GMT

Hi Cynthia,

Thank you very much. I will check with SAS Tech support.

Re: Error on count

deleted_user — Tue, 27 May 2008 13:32:08 GMT

Thank you very much. I will check with SAS Tech support.