topic Re: Data synchronization between SAS library and SQL Server schema in Administration and Deployment

Data synchronization between SAS library and SQL Server schema

Acf2 — Thu, 25 Jul 2024 17:07:40 GMT

We have limited disk space on our SAS94 Windows Server 2019 but lots more space on a SQL Server 2019 instance connected using ODBC. I have started writing a utility to compare metadata and record counts but may need something to ensure data integrity of large numerics. Our DBA is complaining that SAS94 defaults to float[8] and we should use int or bigint.

Has anyone got anything already?

The intention is to load SAS master data into SQL tables which are read or copied back by SAS when needed. Data versioning seems easier on SQL Server.

Re: Data synchronization between SAS library and SQL Server schema

Kurt_Bremser — Thu, 25 Jul 2024 17:29:53 GMT

SAS numbers are 8-byte real, and nothing else. If you want to use the database to accommodate SAS data, it has to use float(8).

Be aware that offloading the data to a remote database might cause serious performance penalties unless you can also push the processing into the DB.

Re: Data synchronization between SAS library and SQL Server schema

AhmedAl_Attar — Thu, 25 Jul 2024 17:42:00 GMT

You & your DBA may need to read this SAS Online documentation https://documentation.sas.com/doc/en/lrcon/9.4/p0ji1unv6thm0dn1gp4t01a1u0g6.htm,

plus these papers

Please note, while the above allows you to utilize larger set of data types, once you are trying to save the data into a SAS 9.4 data set file format (*.sas7bdat), only two data types would be supported (number (8 byte), char)

Hope this helps

Re: Data synchronization between SAS library and SQL Server schema

Acf2 — Fri, 26 Jul 2024 15:09:31 GMT

Thanks for pointing me to the FedSQL paper - very useful reference. proc sql _method lets me look under the covers when testing.

Re: Data synchronization between SAS library and SQL Server schema

Acf2 — Fri, 26 Jul 2024 15:18:45 GMT

The intention is to add data change control which seems more manageable in SQL Server using change tracking. Also, a lot of the data might benefit from partitions - concatenating multiple periods using SQL views for time series analysis. SQL Server seems to have better view performance than SAS but it is more difficult to measure.

Re: Data synchronization between SAS library and SQL Server schema

Acf2 — Fri, 26 Jul 2024 15:35:25 GMT

I forgot to mention that int[] is sufficient for most of our numeric data. We have some key fields stored as long integers which we will store as character in SQL .

Since much of this data is used for modelling, the users have agreed that numeric precision is not a priority compared with storage capacity.