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):
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.
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
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.
it's showing the same error message after removing proc contents.
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.
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.
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.
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.
Neither of the case does work.
Thank you.
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).
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?"
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.
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:
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.