10-29-2012 05:27 PM
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.
** 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;
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:
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):
10-29-2012 05:55 PM
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.
10-30-2012 10:30 AM
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.
10-30-2012 01:53 PM
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.
10-30-2012 02:08 PM
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.
10-30-2012 02:26 PM
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.
10-30-2012 02:46 PM
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.
10-30-2012 02:56 PM
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.
10-30-2012 03:28 PM
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).
10-30-2012 03:35 PM
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?"
10-30-2012 03:41 PM
It appears it might be a FileMaker issue and you could be hooped until you upgrade to V12:
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.
10-30-2012 04:02 PM
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: