data in Library but data does not exist?

Reply
Occasional Contributor
Posts: 11

data in Library but data does not exist?

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):


PROC Star
Posts: 7,362

Re: data in Library but data does not exist?

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.

Occasional Contributor
Posts: 11

Re: data in Library but data does not exist?

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

PROC Star
Posts: 7,362

Re: data in Library but data does not exist?

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.

Occasional Contributor
Posts: 11

Re: data in Library but data does not exist?

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

Super User
Posts: 5,081

Re: data in Library but data does not exist?

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.

Occasional Contributor
Posts: 11

Re: data in Library but data does not exist?

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.

PROC Star
Posts: 7,362

Re: data in Library but data does not exist?

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

preserve_tab_names=no

Occasional Contributor
Posts: 11

Re: data in Library but data does not exist?

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.

PROC Star
Posts: 7,362

Re: data in Library but data does not exist?

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.

Occasional Contributor
Posts: 11

Re: data in Library but data does not exist?

Neither of the case does work.

Thank you.

Super User
Posts: 17,797

Re: data in Library but data does not exist?

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).

Occasional Contributor
Posts: 11

Re: data in Library but data does not exist?

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?"

Super User
Posts: 17,797

Re: data in Library but data does not exist?

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.

Occasional Contributor
Posts: 11

Re: data in Library but data does not exist?

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:

Ask a Question
Discussion stats
  • 18 replies
  • 3632 views
  • 8 likes
  • 4 in conversation