BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
chaitalip
Fluorite | Level 6

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.

ERROR: The product with which the engine ODBC is associated is either not licensed for your system or the product license has
expired. Please contact your SAS installation representative.
 
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
Should be :

data strat.MarksDtls;
 set  Z.MarksDtls;
run;


It seems that I can't edit my post.

View solution in original post

12 REPLIES 12
LinusH
Tourmaline | Level 20

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:

https://communities.sas.com/t5/SAS-Analytics-U/Connect-to-SQL-Server-Oracle-Database-by-SAS-Universi...

Data never sleeps
Ksharp
Super User

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 ;
chaitalip
Fluorite | Level 6

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

Ksharp
Super User
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 .




Ksharp
Super User
Also when you configured the ODBC connection, make sure test it at the last step and it shows success! .

chaitalip
Fluorite | Level 6

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 :

 

ERROR: Server is unable to authenticate user credentials. (missing or invalid SERVERUSER=<username> SERVERPASS=<password> on
libname statement).
ERROR: Error in the LIBNAME statement.
 
When I searched for this error I was directed to "http://support.sas.com/kb/45/460.html" link. which suggested
- setting "Logon as a Batch Job" privilege in the local security policy on the Windows server.
Still i am getting the above error.
 
Pl. Help.
Thanks in Advance.
 
 
Ksharp
Super User

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?

x.png

 

 

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         
Ksharp
Super User

Is that IP is your real IP of PC FILES Server? 

 

Start->type  CMD  at run line -> type   ipconfig /all    to your IPv4 Address.

Ksharp
Super User

USER and PASSWORD  is for DSN when you set up a connection to DB.

NOT 'My System user' 'My System PW'.

chaitalip
Fluorite | Level 6

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.

 


OP.png
Ksharp
Super User
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;



Ksharp
Super User
Should be :

data strat.MarksDtls;
 set  Z.MarksDtls;
run;


It seems that I can't edit my post.

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
  • 12 replies
  • 2617 views
  • 2 likes
  • 3 in conversation