How to export a SAS dataset using SAS DI studio to Microsoft sql server?

Reply
Contributor
Posts: 46

How to export a SAS dataset using SAS DI studio to Microsoft sql server?

How to export a SAS dataset using SAS DI studio to Microsoft sql server?

Hi Folks,

I am using SAS DI studio. I have a sas dataset that needs to be exported to Microsoft sql server. I need in help in how to navigate, establish the ODBC connectivity to sql server and finally export the dataset to make it a sql table. Any guidance would be most appreciated.

Thanks.

Valued Guide
Posts: 3,208

Re: How to export a SAS dataset using SAS DI studio to Microsoft sql server?

Did you ask your platform admin?

You need to have a correct license. Than the installation should have at and the configuration should have set up correctly.

The last step finished with an instruction how to use. 

---->-- ja karman --<-----
Regular Contributor
Posts: 180

Re: How to export a SAS dataset using SAS DI studio to Microsoft sql server?

You have to:

1.  Define an ODBC Library for the SQL Server using the New Library Wizard. This process will need to create a Database Server

2. Use a Table Loader or a User Written Code transformation (Data Step or Proc Append) to copy the SAS table to the SQL Server Database

CTorres

Contributor
Posts: 46

Re: How to export a SAS dataset using SAS DI studio to Microsoft sql server?

Hi Torres/Jaap,

Thanks for the response. For whatever reason, being a newbie, i find it crazy using DI studio, However, with a user written code I managed to get somewhere, however I am encountering a new error-

libname sqlcon odbc user=sa password=newuser123# datasrc="sql connect" ; /*successful connection i guessSmiley Happy*/

LIBNAME targ BASE ("Data" "Data\wrsdist" "Data\wrstemp" "G:\SAS\SASFoundation\9.4\inttech\sample");

data sqlcon.Transaction_Detail;

set targ.trans;

run;

NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

ERROR: CLI execute error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting float to data type

       numeric. : [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.

NOTE: The DATA step has been abnormally terminated.

NOTE: The SAS System stopped processing this step because of errors.

Super User
Posts: 5,380

Re: How to export a SAS dataset using SAS DI studio to Microsoft sql server?

Try add

options sastrace=',,,d' sastraceloc=saslog;

And you will hopefully see what SQL is generated from your SAS program.

Probably you will be assigned a default data type/length in SQL Server based on the SAS data-type and format, which is in conflict with the actual data in your SAS table.

SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Fifth Edition

Check in SQL Server Mgmgt Studio for verifying what the column definitions ended up with.

Data never sleeps
Super User
Posts: 5,380

Re: How to export a SAS dataset using SAS DI studio to Microsoft sql server?

I also urge to make the connection to SQ Server work with meta data registrations.

The carefully design your target table (if that's not already been done by your data modeler) based on your source data. Try to apply SAS formats to the target table meta data. If you still not getting the appropriate design on the SQL Server table, create it separately in SQL Server, then import the meta data into DI Studio.

By firing from the hip you are just getting yourself into trouble, now, or later when your applications is in production.

Data never sleeps
Valued Guide
Posts: 3,208

Re: How to export a SAS dataset using SAS DI studio to Microsoft sql server?

Indeed starting to explore new dataconnection it is crazy to do that with DI.
It is better to start with Eguide or even SAS/Base (dms) to do that. Using Eguide you can use the same WS as Eguide (similar connection profile).

I would advice to start exploring technical issues with Eguide.

It looks you are having a DBMS SQL server connection but somehow there is an failure.

With Eguide you can do the same libname setting and than look at the variable types/definitions as the ODBC interface is suggesting.     
When you can view all data-attributes of the table I assume the interface is working. Possible some tuning needed that is a follow up step.
The message of arithmetic overflow could be an indication of a conversion data-types error.

SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Fifth Edition These are the assumptions on that. You could verify and check for issues. Remember the precision and numerics in SAS are based on floating types (max ca 12 digits). 

---->-- ja karman --<-----
Valued Guide
Posts: 3,208

Re: How to export a SAS dataset using SAS DI studio to Microsoft sql server?

When the odbc connection is working it should be registered in the SAS metdata.

DI is based on metadata objects not on base coding. Do you have a platform admin? 

---->-- ja karman --<-----
Contributor
Posts: 46

Re: How to export a SAS dataset using SAS DI studio to Microsoft sql server?

Hi Jaap/Linus,

Thank you for your responses. Well, if your question is whether do we have a platform administrator. I'm afraid No. We are a brand new team and our sas installation was done only a week ago, However, the tasks are pretty overwhelming,and most of us are from Base SAS programming background, although I am quite comfortable with Eguide.

Do you reckon it's a must to hire an experienced platform administrator?or is it alright for one of us in the team to get trained and start working as an admin?

For the data conversion part, since the dataset is so wide, i wonder what are those variables that requires the conversion. I was of the assumption, SAS DI would do it.  Hmm, designing sounds scary? is it that hard a task to achieve this in DI?It makes me rather wonder, coding based work seems to accomplish faster with more control as opposed to point andclick.  Sorry if I should silly. My apologies.

I will once again carefully do what you suggested the first thing in the morning tomorrow and will message you. I sincerely appreciate this help. Also, please leave a further comment should something cross your mind. Many thanks indeed.

Super User
Posts: 5,380

Re: How to export a SAS dataset using SAS DI studio to Microsoft sql server?

It sounds "crazy" if going to build stuff in DI Studio if you have no experience. You need training and guidance. DI Studio is a lovely tool, but you need to know how to master it.

If it's wide, maybe it need to redesigned, for that, you need to match user requirements and adopt it to a (different?) data model. But you can't really skip designing your SQL Server table properly.

Data never sleeps
Valued Guide
Posts: 3,208

Re: How to export a SAS dataset using SAS DI studio to Microsoft sql server?

Allaluiah Sounds as a quick delivery without proper design. You have to deal with that now and go for an interative proces to master it. I could see it as some agile approach in some sprints learning to swim in a first dive into deep water. The good thing is there are lifebelts.

For planning the platform admin role I would first think on whether one or two getting into that.  I would advice to plan a segregated metadataserver (lev5?) for experimenting/learning and checking.  One of the first thing to know is: how to do reliable backup/restore of metadata to a state you are trusting.

I wonder how LCM or the OS configurations files are getting into this. I would expect this are your company responsabilities. You need possible a platform admin guy understanding that part. It can be learned although requiring some different mindset as common to analysts. 

DI is a good tool developing ETL processes and more using the metadata. It generates SAS code. For a classic educated programmer

The metadata is your source. The delivered SAS code is the runnable version (like an executable) of the metadata. 

The advantages of this will come to life as impact analysis an more numerous elements are getting registered. Sometimes data-flows are designed by dedicated tools eg Erwin. With SAS DI you have all that in one.

Your lifebelts are the knowledge of SAS-base programming.  If you are not sure on some code verify that first using what your are comfortable with (Eguide).

The translation SAS to SQL-server is done by the SAS/Access odbc interface. As DI is a SAS-code generator, it will make use of that.    

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 10 replies
  • 804 views
  • 1 like
  • 4 in conversation