DBTYPE Question

New Contributor
Posts: 2

DBTYPE Question

I have a question with regards to the dbtype option in the data step.  When using SAS 9.1, I'm trying to load a table into Microsoft Access.  I have a variable that is numeric which i want to be converted to the Yes/No variable in Access.  I use the dbtype option to do this conversion.  It is able to work in SAS 9.1, however when our company upgraded to SAS 9.2, it doesn't work.  More specifically, my code for the data step portion is:

data updAcc.activity (dbtype=(sex='YESNO'));

     set places;


In SAS 9.2 when trying to run the code, it produces the following message:

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

ERROR: During insert:

NOTE: The DATA step has been abnormally terminated.

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

NOTE: There were 1 observations read from the data set WORK.places.

WARNING: The data set updAcc.activities may be incomplete.  When this step was stopped

         there were 0 observations and 40 variables.

ERROR: ROLLBACK issued due to errors for data set updAcc.activity.DATA.

NOTE: DATA statement used (Total process time):

      real time           0.56 seconds

      cpu time            0.04 seconds

I tried searching online for a possible solution but couldn't find anything.  I don't know why I have a rollback error.  The number of variables i have in my data set is only 40 which is less than the maximum allowed.  I don't know is it SAS 9.2 and later doesn't support this data type anymore.  Anyway, if anybody can offer a possible solution that would be great.  Thanks!

Super User
Posts: 5,876

Re: DBTYPE Question

As in all trouble shooting process, you need to know what is new, what is changed:

  • SAS host (server, Windows version)
  • MS Access DB (version, location)
  • Data,have you tried to run this using the exact same source data?
  • Libname statement
  • SAS environment (options, license, whatever)

If everything above is exact the same between SAS versions, you probably want to talk to SAS tech support.

Data never sleeps
Super User
Super User
Posts: 8,086

Re: DBTYPE Question

Turn on tracing options for passthrough SQL and you should get examples of what code SQL tried to use in the database.

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

New Contributor
Posts: 2

Re: DBTYPE Question

So I was finally able to get it to work.  I just added the dbmax_text option to the libname engine statement and it was able to work.

Ask a Question
Discussion stats
  • 3 replies
  • 3 in conversation