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,046

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,261

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,046

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: 38

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

Hi, is PC files server still useable?

Super User
Posts: 10,046

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: 38

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.
Ask a Question
Discussion stats
  • 6 replies
  • 1471 views
  • 7 likes
  • 4 in conversation