Data synchronization between SAS library and SQL Server schema

[Acf2]

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.

[Kurt_Bremser]

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.

[Acf2]

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.

[Acf2]

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.

[AhmedAl_Attar]

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

• Proc FEDSQL: https://support.sas.com/resources/papers/proceedings15/3390-2015.pdf
• Proc DS2: https://www.lexjansen.com/wuss/2022/WUSS-2022-Paper-159.pdf
• https://support.sas.com/resources/papers/proceedings18/2160-2018.pdf

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 

[Acf2]

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