topic Re: Outputting SAS Dataset to be viewed in SQL in ODS and Base Reporting

Outputting SAS Dataset to be viewed in SQL

julrik — Fri, 05 Oct 2007 19:24:02 GMT

Hello, I am a new user and know only the basic functions of sas and was assigned a project to figure out how to output a SAS dataset to be viewed in MS SQL Server. I have the dataset created, all I need to know is how I can convert that to a table that SQL server is able to open. The best way I can find is exporting the dataset to a csv file and then writing code in SQL to import the CSV file. But seems like that is an extra step that shouldn't have to be taken and because of the 300+ columns in the dataset I don't want to have to define each one in the SQL code. Can anyone help?

Sorry in advance if I posted in the wrong discussion group but this one sounded close to what I want to do.

Thanks

Re: Outputting SAS Dataset to be viewed in SQL

Cynthia_sas — Fri, 05 Oct 2007 19:42:38 GMT

Hi,
This is not the right forum for this question. In fact, this is really a question that is best answered by Tech Support. To find out how to contact SAS Tech Support, refer to:
http://support.sas.com/techsup/contact/index.html

and you might also find these previous forum postings and Tech Support Notes to be of interest:
http://support.sas.com/forums/thread.jspa?messageID=2624ੀ
http://support.sas.com/forums/thread.jspa?messageID=1946ޚ
http://support.sas.com/forums/thread.jspa?messageID=4121မ
http://support.sas.com/techsup/unotes/SN/006/006450.html
http://support.sas.com/techsup/unotes/SN/011/011332.html
http://support.sas.com/techsup/unotes/SN/009/009733.html

cynthia

Re: Outputting SAS Dataset to be viewed in SQL

deleted_user — Fri, 05 Oct 2007 23:21:48 GMT

Julrik,

Try something like this, I believe you'll need SAS/Access for MS Sql Server to do this. The libname has certain options and depending on how your system is set up you will have to make adjustments to fit your particular needs.

libname sqlsrvr oledb provider=sqloledb properties=("data source" ='DEV9\DEV9' "Database" ='DEV9'
"Integrated Security" ='SSPI' "initial catalog" ='master')
schema=dbo BCP=YES BCP_ERRORFILE="Z:\error_out.txt";

data SAS_DATA_SET_NAME_HERE ;
set datain;
run;

proc sql;
create table sqlsrvr.SQL_SERVER_TABLE_NAME_HERE as
select * from SAS_DATA_SET_NAME_HERE;
quit;