BookmarkSubscribeRSS Feed
happyji
Calcite | Level 5

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


18 REPLIES 18
art297
Opal | Level 21

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.

happyji
Calcite | Level 5

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

art297
Opal | Level 21

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.

happyji
Calcite | Level 5

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

Astounding
PROC Star

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.

happyji
Calcite | Level 5

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.

art297
Opal | Level 21

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

preserve_tab_names=no

happyji
Calcite | Level 5

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.

art297
Opal | Level 21

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.

happyji
Calcite | Level 5

Neither of the case does work.

Thank you.

Reeza
Super User

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

happyji
Calcite | Level 5

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

Reeza
Super User

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.

happyji
Calcite | Level 5

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:

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 18 replies
  • 8592 views
  • 8 likes
  • 4 in conversation