Learning SAS? Welcome to the exclusive online community for all SAS learners.

Connect to SQL Server/Oracle Database by SAS University Edition

Reply
Super User
Posts: 10,681

Connect to SQL Server/Oracle Database by SAS University Edition

[ Edited ]

As @

 

 

 

 

LIBNAME z PCFILES SERVER='sjfx01' DSN=ksharp USER=xxx   PASSWORD=xxxxx SCHEMA=dbo ;
LIBNAME z PCFILES SERVER='192.168.60.1' DSN=ksharp USER=xxx   PASSWORD=xxxxx SCHEMA=dbo ;

 

52         LIBNAME z PCFILES SERVER=sjfx01 DSN=ksharp USER=cbcuser001  PASSWORD=XXXXXXXX SCHEMA=dbo ;
 NOTE: Libref Z was successfully assigned as follows: 
       Engine:        PCFILES 
       Physical Name: ksharp

 

54         data x;
 55          set z.Customs;
 56         run;
 
 NOTE: There were 98 observations read from the data set Z.Customs.
 NOTE: The data set WORK.X has 98 observations and 9 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.06 seconds
       cpu time            0.02 seconds
       
 
 57         data companyareas ;
 58          set z.companyareas ;
 59         run;
 
 NOTE: There were 84 observations read from the data set Z.companyareas.
 NOTE: The data set WORK.COMPANYAREAS has 84 observations and 5 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.06 seconds
       cpu time            0.02 seconds
       
 
 60         data agents ;
 61          set z.agents ;
 62         run;
 
 NOTE: There were 1884 observations read from the data set Z.agents.
 NOTE: The data set WORK.AGENTS has 1884 observations and 8 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.09 seconds
       cpu time            0.03 seconds
       
 
 63         data provinces ;
 64          set z.provinces ;
 65         run;
 
 NOTE: There were 35 observations read from the data set Z.provinces.
 NOTE: The data set WORK.PROVINCES has 35 observations and 7 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.07 seconds
       cpu time            0.02 seconds
       
 
 66         data roles ;
 67          set z.ROLES;
 68         run;
 
 NOTE: There were 18 observations read from the data set Z.ROLES.
 NOTE: The data set WORK.ROLES has 18 observations and 7 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.06 seconds
       cpu time            0.02 seconds

 

proc sql;
   connect to PCFILES(SERVER=sjfx01 user=xxxxx  password=xxxxx        
                   dsn=ksharp );
   create table work.odbcSchema as
          select * from connection to PCFILES 
          (PCFILES::SQLTables "","","","");
quit;

 

 

 

Super User
Posts: 3,856

Re: Connect to SQL Server/Oracle Database by SAS University Edition

That is totally cunning - using the PC File Server to connect via ODBC to an RDBMS! Thanks for the great tip @Ksharp and Reeza.

Super User
Posts: 10,681

Re: Connect to SQL Server/Oracle Database by SAS University Edition

Yes. I was thrill too. Finally We can get SAS and DB together without costing any money . Isn't it beautiful ?

Contributor
Posts: 42

Re: Connect to SQL Server/Oracle Database by SAS University Edition

Hi, is PC files server still useable?

Super User
Posts: 10,681

Re: Connect to SQL Server/Oracle Database by SAS University Edition

Sorry . I don't try it for a very long time.
And the speed of transferring data is very very slow , which make it look like useless.


Contributor
Posts: 42

Re: Connect to SQL Server/Oracle Database by SAS University Edition

I see, thanks for your reply.
I guess I will be looking for a different way.

Frequent Contributor
Posts: 136

Re: Connect to SQL Server/Oracle Database by SAS University Edition

I used it a few days ago to access a SQLite database schema - OK, not blindingly fast but not annoyingly slow either, and a VERY handy way to access multiple data tables in complex DB schema in a variety of proprietary formats and get the metadata too. I have used it with PostgreSQL and Microsoft SQL Server previously since switching to SAS Studio UE. I would not have tried it if not for your original post. Thanks Ksharp. Much appreciated.
Senior User
Posts: 1

Re: Connect to SQL Server/Oracle Database by SAS University Edition

Posted in reply to Damien_Mather

I'm only replying to let you know that this worked perfectly for me with Postgres. It would be nice if the schema could be listed in the libraries though, but this is definitely better than nothing.

New User
Posts: 1

Re: Connect to SQL Server/Oracle Database by SAS University Edition

[ Edited ]

There is error when I start sas pc files server: unable to connect to service port 9621

Please help me to fix this. Thanks/ 

(I setup SAS University Edition without SAS/ACCESS and trying to connect SAS UE to SQL Server using SAS PC File  Server)

Super User
Posts: 10,681

Re: Connect to SQL Server/Oracle Database by SAS University Edition

Posted in reply to CuongPham

If that problem is about  pc files server, sorry I can't help .

Search the error info at support.sas.com 

Ask a Question
Discussion stats
  • 9 replies
  • 2495 views
  • 8 likes
  • 6 in conversation