topic Re: data in Library but data does not exist? in SAS Programming

data in Library but data does not exist?

happyji — Mon, 29 Oct 2012 21:27:51 GMT

Hello Everyone,

I am trying to connect FM server data (here, PSB) into SAS using ODBC engine.

FM server drive was successfully installed for ODBC. Toad data point 3.2 also can connect FM server and I can see the data through Toad.

My problme is that it seems SAS connected the FM server with ODBC becasue I can see the list of all data table in the library. Proc contents also show the list of all data from the server.

However, when I tried to see the each table using proc sql, the error message comes up, saying the data does not exist.

I am stumped becase the libray has the list of all tables but cannot open each table.

Did anyone have this kind of problem happened before?

Please, help me to access the each table.

Thank you in advance.

eunji

** I am using SAS 9.3TS1M0 with W32_7PRO platform.

*My SAS code;

libname psb odbc dbprompt=yes defer=yes datasrc=PSB user=XXX password=XXXXXXX access=readonly preserve_tab_names=yes;

proc contents data=psb._ALL_ nods; run;

proc sql;create table work.participants as select * from psb.CANCERS;quit;

*SAS log;

16 libname psb odbc dbprompt=yes defer=yes datasrc=PSB user=XXX password=XXXXXXX access=readonly

16 ! preserve_tab_names=yes;

NOTE: Libref PSB was successfully assigned as follows:

Engine: ODBC

Physical Name: PSB

17 proc contents data=psb._ALL_ nods; run;

NOTE: PROCEDURE CONTENTS used (Total process time):

18 proc sql; create table work.participants as select * from psb.CANCERS;

ERROR: File PSB.CANCERS.DATA does not exist.

18 ! quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

Re: data in Library but data does not exist?

art297 — Mon, 29 Oct 2012 21:55:36 GMT

Is FM FileMaker? If so, what version are you on? one thing you could try is to reissue the libname statement just prior to issuing the proc sql code.

Re: data in Library but data does not exist?

happyji — Tue, 30 Oct 2012 14:30:34 GMT

Thank you for your interest, Arthur.

My FM filemaker is v11.

How to reissue the libname statement? I am googling about this but don't know what that means.

Thank you,

eunji

Re: data in Library but data does not exist?

art297 — Tue, 30 Oct 2012 17:53:54 GMT

It is just a thought based on what i saw via a Google search. There were 3 parts to the code you ran: libname, proc contents and proc sql.

What I'm wondering is whether the file will be found if you remove the proc contents part, thus only have the libname and proc sql parts in your code.

Re: data in Library but data does not exist?

Astounding — Tue, 30 Oct 2012 18:08:33 GMT

Just a mildly educated guess here ...

In most cases, SAS has worked out how to locate the proper data set when capitalization is different. Perhaps this is a case that hasn't been worked out. Make sure the table name (CANCERS vs. Cancers vs. cancers) matches capitalization exactly with the table you are trying to pull.

Good luck.

Re: data in Library but data does not exist?

happyji — Tue, 30 Oct 2012 18:15:33 GMT

it's showing the same error message after removing proc contents.

Re: data in Library but data does not exist?

happyji — Tue, 30 Oct 2012 18:26:20 GMT

Astounding,tThank you for your reply.

I think the option of preserve_tab_names=yes in libname keeps the same table name.

All table names in the database is capitalized.

Re: data in Library but data does not exist?

art297 — Tue, 30 Oct 2012 18:35:50 GMT

may be onto something. I would try it with the exact opposite option, namely:

preserve_tab_names=no

Re: data in Library but data does not exist?

happyji — Tue, 30 Oct 2012 18:46:41 GMT

There are 78 tables included in the library PSB.

If preserve_tab_names=no, then only 66 tables were in the list of library becasue some of the table name has space.

For example, the table with "ABS SURGERY" cannot be read.

Re: data in Library but data does not exist?

art297 — Tue, 30 Oct 2012 18:56:33 GMT

Understood, but will using that option allow you to access the cancers table via proc sql? If it will, Astounding would have been correct and you have to discover the true case of the spelling of your files.

Re: data in Library but data does not exist?

happyji — Tue, 30 Oct 2012 19:22:02 GMT

Neither of the case does work.

Thank you.

Re: data in Library but data does not exist?

Reeza — Tue, 30 Oct 2012 19:28:46 GMT

You said you can see them in the library. What happens when you open them directly from the library, does it open (assuming your using Windows here).

Re: data in Library but data does not exist?

happyji — Tue, 30 Oct 2012 19:35:13 GMT

When I trid to open directly from the library, the SAS error window pops up, saying "The table either does not exist or cannot be dispayed. Would you like to choose a different tables to display?"

Re: data in Library but data does not exist?

Reeza — Tue, 30 Oct 2012 19:41:55 GMT

It appears it might be a FileMaker issue and you could be hooped until you upgrade to V12:

http://forums.filemaker.com/posts/c2d843a1ba?page=1

One thing to try, connect to it via odbc and using Microsoft Access, take a look at the table names in Access. I once had a connection that had a weird schema that wasn't specified anywhere else. Once I added that in to my connection string in SAS things worked perfectly.

Re: data in Library but data does not exist?

happyji — Tue, 30 Oct 2012 20:02:21 GMT

Reeza, thank you!

I knew that FileMaker issue is here. Unforunately, the data were entered in FM V11.

A person from FileMaker confirmed that FM V12 cannot read the files in V11 :smileyshocked:.

I will try the way you have done and keep it posted.

In the mean while, I connected the FM server into R using a package of RODBC and read the data into SAS using PROC IML. This works!:smileylaugh:

However, another problem comes up. R missed some objecets from the table. :smileycry:

Re: data in Library but data does not exist?

Reeza — Tue, 30 Oct 2012 20:05:27 GMT

If you can connect an Access DB to the FM table successfully, you might be able to use SAS to connect to Access.

Re: data in Library but data does not exist?

happyji — Tue, 30 Oct 2012 22:00:41 GMT

I could connect the FM tables to Access. How do I conncet SAS to Access?

Re: data in Library but data does not exist?

Reeza — Tue, 30 Oct 2012 22:13:53 GMT

libname test access 'path to your access project here';

That usually does it I think. Unless your in Access 2007 and above, then it might be AccessCS or you may need to change things.

Re: data in Library but data does not exist?

happyji — Tue, 30 Oct 2012 23:03:47 GMT

Thank you, Reeza.

This works! I probably use this way to connect FM server unless I find anothr way to directly connect FM server into SAS.