- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I am using SAS University edition and want to access the data from SQL server on my host machine(Windows 8.1 64 bit) in my SAS code. Virtual Machine(Oracle VM Virtualbox) OS is also Windows 8.1 64 bit. I am getting following error.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Should be : data strat.MarksDtls; set Z.MarksDtls; run; It seems that I can't edit my post.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Generally, ODBC connections are not supported in SAS UE - there are many posts that concludes that.
But, @Ksharp has put forward a hack for a work-around - see if that is applicable to your situation:
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
HaHa.Yes. UE can talk to DB .
Also try IP Address :
LIBNAME z PCFILES SERVER='192.168.56.1' DSN=ksharp USER=xxxxxxx PASSWORD=xxxxxxx SCHEMA=dbo readbuff=10000 access=readonly ;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi ksharp,
As per your guidelines I have installed PCFiles and configured the ODBC connection. But, when trying to execute following statements in SAS University Edition
libname MyLib '/folders/myfolders/';
LIBNAME MyLib PCFILES SERVER=SERVER DSN=SAS USER='sa' PASSWORD='database@1' ;
I am getting following errors.
- Libref MyLib is not assigned.
- Error in LIBNAME Statement.
- Invalid Option Name.
Also the Library created by "libname MyLib '/folders/myfolders/';" statement disappears.
Pl. help.
Thanks, in advance
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
You also need specify SCHEMA and Try IP address if you are using Oracle Visual Box . Copy my code and change SERVER='your IP address' "Also the Library created by "libname MyLib '/folders/myfolders/';" statement disappears." Because you use the same library name MYLIB in libname, try other name Like Z .
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Also when you configured the ODBC connection, make sure test it at the last step and it shows success! .
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
As suggested by you i have followed the entire procedure as below,
LIBNAME z PCFILES SERVER='My IP Address' DSN=SAS USER='My System user' PASSWORD='MySystem password' SCHEMA=dbo readbuff=10000 access=readonly ;
but, I am getting following error :
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
1) Did you start PC FILES by hand ? Not as a system service to start ?
2)Did you set up a password for your PC FILES Server?
3)My PCFILES Server and UE are on the same PC(laptop), OS is Windows 7 Professional , UE is download before two weeks .
Can you change your OS or PC . Just a normal computer , not Server Configure .
Here is my LOG:
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
51
52 LIBNAME z PCFILES SERVER='192.168.56.1' DSN=ksharp USER=cbcuser001 PASSWORD=XXXXXXXX SCHEMA=dbo readbuff=10000
52 ! access=readonly ;
NOTE: 已成功分配逻辑库引用名“Z”,如下所示:
引擎: PCFILES
物理名: ksharp
53
54
55 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
65
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Is that IP is your real IP of PC FILES Server?
Start->type CMD at run line -> type ipconfig /all to your IPv4 Address.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
USER and PASSWORD is for DSN when you set up a connection to DB.
NOT 'My System user' 'My System PW'.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi KSharp,
Thanks for your reply. It helped. Now, I am able to access the SQL Server but, have one more query.
How do i get the data from specific table of my sql database which i have selected as default database in my DSN.
CODE :
libname MyLib '/folders/myfolders/';
LIBNAME z PCFILES SERVER='MyIP' DSN=SAS USER=sa PASSWORD=database@1
SCHEMA=dbo readbuff=10000 access=readonly;
%let path = /folders/myfolders/;
libname strat "&path";
proc sql ;
connect to PCFILES(SERVER='my IP' DSN=SAS USER=sa PASSWORD=database@1);
create table work.stud as select* from connection to PCFILES
(PCFILES:: SQLTables "", "", "", "");
quit;
proc print data=work.stud;
run;
OUTPUT: Attached.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Once you successfully connect to DB , that is going to be easy to pull data out from them. You can do the same thing as I post : %let path = /folders/myfolders/; libname strat "&path"; data strat.MarksDtls; set MyLib.MarksDtls; run; Or if you need pull out many tables. Try this: data _null_; set work.stud (where=(table_schem='dbo')); call execute(cats('data strat.',table_name,'; set MyLib.',table_name,';run;')); run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Should be : data strat.MarksDtls; set Z.MarksDtls; run; It seems that I can't edit my post.