Prev: Weighted Average
Next: Macro + Surveyselect
From: syam on 6 Oct 2006 06:27
Hi All,

I would like to import Ms SQL server data table into SAS.

Can anybody help me out by providing steps to follow and sample code
for importing sql server data table into SAS. Also, Plz explain me
different ways of creating connections and using this connecitons for
importing data into SAS.

We have SAS/Access also.

Thanks in advance.
Syam

From: "Terjeson, Mark" on 6 Oct 2006 10:45
Hi Syam,



* Note: an ODBC entry needs to be made to ;
* point to the desired SQL Server. ;



* this subquery inside the parentheses ;
* is pass-through query code, meaning ;
* the subquery text gets passed to the ;
* sql server box and actually run there ;
* therefore the outer query is SAS ;
* syntax and the subquery is SQL Server ;
* syntax. this query gets back rows. ;
proc sql;
connect to sqlsvr(uid=mysqlsvrid pwd=mypwd dsn=myodbctoken);

create table MyResults as
select *
from connection to sqlsvr
(
select *
from MySqlServerDatabase.dbo.MyTable
)
;

disconnect from sqlsvr;
quit;



* if you do not wish rows to come back ;
* you can still execute SQL Server ;
* commands that do not return rows. ;
proc sql;
connect to sqlsvr(uid=mysqlsvrid pwd=mypwd dsn=myodbctoken);

execute(
drop table MySqlServerDatabase.dbo.MyTable
)
;

disconnect from sqlsvr;
quit;



* your odbc connect specifies which ;
* database to point to as the default ;
* when the connection is made. if you ;
* are going to work with tables in ;
* that default database you can, as ;
* an option, not provide the full spec ;
* i.e. MySqlServerDatabase.dbo.MyTable ;
* can be dbo.MyTable ;
* or just MyTable ;


* the odbc connection entry only points;
* to one database. to talk to any of ;
* the other databases on that server ;
* you just specify them: ;
* MySqlServerDatabase.dbo.MyTable ;
* TheOtherDatabaseA.dbo.TheTable ;
* TheOtherDatabaseB.dbo.TheTable ;
* TheOtherDatabaseC.dbo.TheTable ;



* also if you see the two dot notation ;
* such as: ;
* MySqlServerDatabase..MyTable ;
* the two dot notation defaults to dbo ;
* so ;
* MySqlServerDatabase.dbo.MyTable ;
* and ;
* MySqlServerDatabase..MyTable ;
* are the same thing. ;



* datastep and procs can also work ;
* with the remote data by setting ;
* up a library connection with the ;
* LIBNAME statement ;
libname xyz sqlsvr uid=mysqlsvrid pwd=mypwd dsn=myodbctoken;

data result;
set xyz.MyTable;
run;


* create table on database, presuming ;
* you have write permissions as well ;
* as read permissions. ;
data xyz.NewTable;
set xyz.OldTable;
run;







Hope this is helpful.


Mark Terjeson
Senior Programmer Analyst, IM&R
Russell Investment Group


Russell
Global Leaders in Multi-Manager Investing






-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of
syam
Sent: Friday, October 06, 2006 3:27 AM
To: SAS-L(a)LISTSERV.UGA.EDU
Subject: Importing data from MS SQL server to SAS 9.1

Hi All,

I would like to import Ms SQL server data table into SAS.

Can anybody help me out by providing steps to follow and sample code for
importing sql server data table into SAS. Also, Plz explain me different
ways of creating connections and using this connecitons for importing
data into SAS.

We have SAS/Access also.

Thanks in advance.
Syam
From: nevin.krishna on 6 Oct 2006 11:28
Hi Mark,
i have also recently been playing with such connections: i can
accomplish the sql pass thru method using odbc..but when i try the the
data step method you described i get an error saying "sqlsvr engine
cannot be found"...is there a way to use the same method (libname
datastep method) with odbc?

thanks,
Nevin




> * datastep and procs can also work ;
> * with the remote data by setting ;
> * up a library connection with the ;
> * LIBNAME statement ;
> libname xyz sqlsvr uid=mysqlsvrid pwd=mypwd dsn=myodbctoken;
>
> data result;
> set xyz.MyTable;
> run;
>

From: "Terjeson, Mark" on 6 Oct 2006 12:14
Hi Nevin,

Yes you can.

libname x odbc dsn=thetoken;

For the sqlsvr engine you can run PROC SETINIT
to see if you have the license for the sqlsvr
engine. It would look like the line below:

proc setinit;
run;

--SAS/ACC-Microsoft SQL Server


For other examples from our most excellent archives,
check each of these links out for more examples:

http://listserv.uga.edu/cgi-bin/wa?A2=ind0409D&L=sas-l&P=R2206
http://listserv.uga.edu/cgi-bin/wa?A2=ind0603D&L=sas-l&P=R24628
http://listserv.uga.edu/cgi-bin/wa?A2=ind9911A&L=sas-l&P=R6343
http://listserv.uga.edu/cgi-bin/wa?A2=ind0010A&L=sas-l&P=R17707




Hope this is helpful.


Mark Terjeson
Senior Programmer Analyst, IM&R
Russell Investment Group


Russell
Global Leaders in Multi-Manager Investing







-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of
nevin.krishna(a)GMAIL.COM
Sent: Friday, October 06, 2006 8:29 AM
To: SAS-L(a)LISTSERV.UGA.EDU
Subject: Re: Importing data from MS SQL server to SAS 9.1

Hi Mark,
i have also recently been playing with such connections: i can
accomplish the sql pass thru method using odbc..but when i try the the
data step method you described i get an error saying "sqlsvr engine
cannot be found"...is there a way to use the same method (libname
datastep method) with odbc?

thanks,
Nevin




> * datastep and procs can also work ;
> * with the remote data by setting ;
> * up a library connection with the ;
> * LIBNAME statement ;
> libname xyz sqlsvr uid=mysqlsvrid pwd=mypwd dsn=myodbctoken;
>
> data result;
> set xyz.MyTable;
> run;
>
 | 
Pages: 1
Prev: Weighted Average
Next: Macro + Surveyselect