Writing data to SQLSRV table

[HafizhNugraha]

Hi,

I have to write data to SQLSRV table, this table will be used for backup/history table.

I am using DI to write the data to SQLSRV table. It is a simple DI jobs, Source table - Table Loader - SQLSRV table.

i have rechecked the format and informat between source and SQLSRV tables and found no differences.

But when i tried to sum one of the column between Source table and SQLSRV table, the sum value is not same.

The difference is not that wide, 0.1 - 0.5 if the record number reached 120.000. 1-6 when the record reached 1 million. But for record reached 27 million, the gap is in the hundreth.

I do not know much about this, can someone kindly help me explaining this problem?

Thanks.

[JBailey]

Hi @HafizhNugraha 

This is likely due to the data type chosen for the database table. Do you know the SQL Server data type for the column in question?

Small code examples would be helpful, too. 

Best wishes,

Jeff

[HafizhNugraha]

Hi @JBailey  Thanks for your reply

I do not know the data types, because i used DI as interface the data types shown is either character or numeric, but i will try to look further.

The DI jobs consist of 2 transformations, an Extract and a table Loader.

The code filled with many DI generated codes and macro but the code goes like :

%include "....../autoexe.sas"
%include "...../deletedate.sas" /*precode for delete date macro*/
%include "....../bussiness_date.sas" /*precode to get bussiness date*/

/*this is extract transformation*/

proc datasets lib=work nolist nowarn memtype = (data view);
delete worktable;
quit;

proc sql;
create view worktable as
select ID, bussines_date, Outstanding, balance
from Output.sourcetable
where business_date="&cur_bussinessdt"d;
quit;

/*end of extract transformation*/
/*table loader transformation*/

LIBNAME WBLIB SQLSVR Insertbuff=#### Schema=dbo AUTHDOMAIN="MSSQLAuth";

%DeleteCurrentDate(WBLIB, SQLTabble, Bussiness_date);
 *macro to delete data with current date if any;

%let previousSQLtable=SQLtable;


data WBLIB.SQLtable;
set worktable;
attrib ID length=$50
informat = $CHAR50.
format = $CHAR50.;
attrib bussiness_date length=8
informat = date.
format = DATE9.;
attrib Outstanding length=8
informat = 21.2
format 21.2;
attrib balance length=8
informat 21.10
format 21.10;
run;

proc append base = WBLIB.SQLtable
 data = previousSQLtable force;
run;

[Patrick]

The one thing which stands out is the informat of 21.2

attrib Outstanding length=8 informat = 21.2
format 21.2;
attrib balance length=8 informat 21.10
format 21.10;

SAS can only store 15 digits with full precision (16 digits up to a certain number). A informat of 21.d indicates that your source data also resides in a database and not in SAS and though when moving big numbers from a DB to SAS to a DB you'll loose precision within SAS.

The code you've posted doesn't show where previousSQLtable comes from.

proc append base = WBLIB.SQLtable
 data = previousSQLtable force;
run;

If loading from one database table into another database table on the same database server then make sure that your process executes fully in-database and data doesn't get pulled into SAS as an intermediary step. Eventually use SQL INSERT and not Proc Append with the table loader.

As a side note:

I hope you're not using fully qualified path names with the %include statements. I'd recommend that you store these .sas programs in a folder which is part of the SAS Autocall facility so you can just call the macros without the need of any %include statements.

If you really must use %include then at least make sure that you've got the path to your project defined in an autoexec so that when moving to another environment (i.e. from Lev2 to Lev1) there is a single place where change is required (and NOT within the DIS job). 

Try and implement DIS jobs always environment aware so that no change is required as part of promotion.

And as a 2nd side note:

attrib balance length=8 informat 21.10

The informat also indicates that you're dealing with floating point numbers (decimals). Because the data representation differs between environments numbers like 0.1 will not be exactly the same on a binary level. It's not the issue you're currently raising as the difference is only in the very last bits - BUT if you want to guarantee the exact same number then you need to also round() the numbers on the target table side - like for 21.10 a rounding to the 12th digit or so like round(<variable>,0.000000000001)

[HafizhNugraha]

Thank you for your kind reply @Patrick 

i appreciate your precious advices. Yes, we are using fully path name on this project. 

The sum differences issue has been solved temporarily by my colleague, we join the SAS table and SLQSVR table

on a work table. Then we summed the column in work table. The result of sum is same (i do not know why, but my colleague said it was because the difference of summary in SQL table). We showed it to the clients and they acknowledged it and closed the issue.

Are there any advices to avoid this issue next time?

[Patrick · Accepted solution]

"we join the SAS table and SLQSVR table on a work table"

If I get that right then now you're pulling both a source and a target table from a database into SAS before comparing. This way you're loosing precision on both sides and that's why the sums are now the same. 

The only way to not loose precision when moving big numbers (>15 digits) from a database to SAS to a database is to cast the number to a string on the source side, move the data, cast it back to a number on the target side. 

[JBailey]

Hi @HafizhNugraha 

To be entirely sure we would need to see how the SQL Server table is defined, but I feel certain that the data you are putting into SQL Server is losing precision. This is why the numbers don't add up. 

@Patrick provides great advice in his response.

I would suggest coding a simple example with a few observations. Write them to a test table (created using the same numeric data type defined in your target table). You will be able to easily see what is happening. 

Best wishes,

Jeff