Error to export datetime into SQL Server

[masiqmoselli]

Hello all,

I'm trying to export SAS data to SQL Server. This date comes from a SQL Server and goes to another instance (SQL Server as well), whenever I try to export a number or text it run nicely, but on datetime I got an error: 

NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
ERROR: Error attempting to CREATE a DBMS table. ERROR: CLI execute error: [SAS][ODBC 20101 driver][Microsoft SQL Server]Line 1: 
       Specified scale 27 is invalid..

I'm simply trying to export this table from my work, to SQL Server, this happens just when I try to export this datetime column. 

Some infos about the table:

• Just two columns, the first is number and the second is datetime.
• SAS is recognizing datetime informat as: datetime22.3;
• Trying to do it with proc sql:

PROC SQL;
CREATE TABLE SQLSERVER.table2 AS SELECT c1_number, c2_date FROM work.table; QUIT;

Thanks in advance.

[jimbarbour]

So are you taking data from one SQL Server table, pulling it into SAS, and then writing it out to a second SQL Server table?  I assume that's what you're doing since you're specifying a WORK.table in your Create Table AS (CTAS) type SQL.

I think their is some kind of conversion going on.  SQL Server uses one basis for datetime (number of seconds since Jan 1, 1900) whereas SAS uses another (number of seconds since Jan 1, 1960).  I suspect that one has to do a conversion as you push data to SQL Server.

Have you first verified that the datetime stamps coming from SQL Server make sense?  If they make sense, then I assume that SAS is automatically compensating for the difference between SQL Server datetime and SAS datetime as you pull data.  If not, you probably need to apply a conversion when you pull from as well as push to SQL Server.

Jim

[masiqmoselli]

Hello Jim,

Yes probably SAS is making some conversion, the date on SQL Server makes sense the format is: yyyy-mm-dd:hh:mm:ss. But I searched for this format and SAS do not have specifically this one, so I tried to use informat as SAS specifies: datetime22.3, and anothers like: datetime20., datetime20.2.

I tried the conversion, returning each part of the date: year, month, day, etc. And put it together in an string, and try to convert into SAS date. But since SAS don't have this format, I got this errors. 

[jimbarbour]

If I run this code:

%LET	SQL_Server_Basis_d	=	'01Jan1900'd;
%LET	SQL_Server_Basis_dt	=	'01Jan1900:00:00:00'dt;
%LET	SAS_Basis_d			=	'01Jan1960'd;
%LET	SAS_Basis_dt		=	'01Jan1960:00:00:00'dt;

%LET	Difference_Days		=	%SYSEVALF(&SAS_Basis_d	-	&SQL_Server_Basis_d);
%LET	Difference_Secs		=	%SYSEVALF(&SAS_Basis_dt	-	&SQL_Server_Basis_dt);

OPTION	NOSOURCE;
%PUT	NOTE:  &=Difference_Days;
%PUT	NOTE-  &=Difference_Secs;
OPTION	&Save_Opts;

I get these results:

NOTE:  DIFFERENCE_DAYS=21914
       DIFFERENCE_SECS=1893369600

Try adding 1893369600 to your SAS datetime in WORK.table before you do your CTAS query.  If the scale error disappears, then we probably have identified the basic problem.  We may need to make some adjustments from there, but we'll at least know the underlying problem.

Jim

[masiqmoselli]

Got the same result here:

NOTE: DIFFERENCE_DAYS=21914
DIFFERENCE_SECS=1893369600

[jimbarbour]

OK, good.  We have the same values in terms of the difference between SAS and SQL Server.  

Have you tried adding the difference to the datetime value in SAS before you try to write to SQL Server?  I'm hoping this will eliminate the scale error.  We still would need to confirm that the actual values match the original table, but I'd like to see if this eliminates the scale error. 

Jim

[masiqmoselli]

Using this PROC SQL returns missing in SQL.

PROC SQL;
INSERT INTO SQLSERVER.table
VALUES( 2, '01Jan1900:00:00:00'dt) ;
quit;
run;

Using to insert in a SAS table, the value is inserted.

PROC SQL;
INSERT INTO work.table
VALUES( 2, '01Jan1900:00:00:00'dt) ;
quit;
run;

Trying to use the value 1893369600 in SQL server I got missing value, and on SAS returned this error:

PROC SQL;
INSERT INTO work.table
VALUES( 2, '1893369600'dt) ;
quit;
run;

ERROR: Invalid date/time/datetime constant '1893369600'dt.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

[masiqmoselli]

Using this code, I got an error on SAS and for SQL Server printed missing:

PROC SQL;
	INSERT INTO work.table
	VALUES( 2, '1893369600'dt) ;
quit;
run;

ERROR: Invalid date/time/datetime constant '1893369600'dt.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

Using this one, in SAS it worked, and on SQL Server missing:

PROC SQL;
	INSERT INTO work.table
	VALUES( 2, '01Jan1900:00:00:00'dt) ;
quit;
run;

[jimbarbour]

The number 1893369600 is just that, a number.  It's the number of seconds different between the way SAS represents a datetime value vs. how SQL server represents a datetime value.  The number 1893369600 cannot be used in a '1893369600'dt construct.  What I'm suggesting is that you add 1893369600 to the datetime values in your SAS Work dataset and see if the scale error disappears.  Remember, a SAS datetime value is stored as the number of seconds.  No matter how a datetime value is formatted, a datetime value is stored as a number.  By adding 1893369600, we adjust for the difference between how SAS represents datetime values vs. how SQL server represents datetime values.  We may not be completely done at that point, but perhaps we can get rid of the scale error.  Maybe.  I've encountered this representation difference before with Excel, so I'm thinking this may be the same issue, so I'm suggesting we try this.

The code would look something like this:

**  First get a datetime variable (column) from the original SQL Server table  **;
PROC SQL;
  CREATE TABLE WORK.MySASWorkTable  AS
    SELECT Some_Datetime_Var FROM SQLsrvr1.TheOriginalTable;
QUIT:

**  Second add 1893369600 to the Datetime value  **;
DATA   Work.Adjusted_Values;
  SET  WORK.MySASWorkTable;
   Some_Datetime_Var = Some_Datetime_Var + 1893369600;
RUN;

**  Third, create the new SQL Server table  **;
PROC SQL;
  CREATE TABLE SQLsrvr2.TheNewTable  AS
    SELECT * FROM WORK.MySASWorkTable;
QUIT:

Jim

[masiqmoselli]

Hi Jim,

Sorry for the misunderstanding. So I've executed as you asked and got the same error when exporting to SQL Server. The sum of the dates went well on SAS.

NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
ERROR: Error attempting to CREATE a DBMS table. ERROR: CLI execute error: [SAS][ODBC 20101 driver][Microsoft SQL Server]Line 1: 
       Specified scale 27 is invalid..

[jimbarbour]

OK, well, so much for that idea.  It sounds like that's not the issue.

I found this in a Google search:

https://knowledgebase.progress.com/articles/Article/Specified-scale-27-is-invalid-error-with-SQL-Ser...

Perhaps it's the driver?

Jim

[masiqmoselli]

Hello Jim, we make this configuration as you suggest. On 9.4 it worked, but did not worked on viya. Trying to reach other solution for Viya.

[s_lassen · Accepted solution]

The problem may be that SAS generally puts timestamps into the SQL Server DATETIME data type. This may not have enough precision for 3 decimals.

I suggest you try something like 

PROC SQL;
  CREATE TABLE SQLSERVER.table2(DBTYPE=(c2_date='DATETIME2'))  AS
  SELECT c1_number, c2_date FROM work.table; 
QUIT;

as the DATETIME2 datatype has sufficient precision.

[masiqmoselli]

Thanks @s_lassen using the DBTYPE worked, I created the table and it allows me to simply insert data latter.

@jimbarbour Thank you as well, the link you sent also worked, and is a configurantion for the 9.4.