Reading SQL Server data from SAS

[Buster49]

We want to use SAS to read data from SQL Server databases, we do not want to write. Is acquiring SAS/ACCESS the only way?

 

[jymm]

sorry - been a while since we bought SAS and I do not remember.

Try this
set up an ODBC connection to your db and a simple sample table

libname thisdb odbc
	dsn=sqlserver datasrc="ReportDB" BCP=Yes;

Data thisdb.sampletable;
Modify thisdb.sampletable;
remove;
Run;

Now that chunk should remove all of the data in that table.

You should also be able to

proc append data=NewRecs base=thisdb.sampletable force;

to append a new record from the sas dataset 'newrecs' into Sample table

and

Data StuffFromDb;
Set thisdb.sampledb;

to simply read the data in.

Let me know if you need any samples - I backed off SAS a few years back when it got pricey and I could do the non-statistical stuff easier in SQL and Crystal (products we had in house) instead of spending $15k + on SAS.

 

[klaz2002]

Buster49,
You need SAS/Access and either ODBC or OLEDB engines. There may be another way but this way is supported by SAS.
Klaz

 

[dje]

I use SAS Access to ODBC also use SAS Access PC file formats.

The code I use is as follows (you will modify the variables selected and the tables used as needed for your task) :

proc sql;
connect to ODBC(dsn=yourodbcname uid=yourid pwd=yourpw );
create table work.mysasfile as
select * from connection to odbc(select
f1.seqnum, f1.detcode, f1.result,s2.lot,
s2.test_date, s2.ident, s2.prod_id, s2.unitcode, s2.profcode ,f5.detname ,f5.unit,
p1.prodname, s7.unitname ,s6.profname
from result f1, samples s2 ,determnt f5 ,product p1, unit s7 , profile s6

where s2.seqnum > &seqnum
and f1.seqnum = s2.seqnum and ( f1.result < -1 or f1.result > -1)
and f5.detcode=f1.detcode and s2.prod_id=p1.prod_id
and s2.unitcode=s7.unitcode and s2.profcode=s6.profcode
ORDER BY F1.DETCODE
);

 

[klaz2002]

DJE,
You still need to buy (license) the ODBC engine from SAS. Buster49 asked does he/she need to buy that license.
Klaz

 

[dje]

True...I use the access to odbc and the access to pc file formats.