Migrating Sas dataset to ms sql database

[mokwenak]

SAS 9.4 M5 ,I’m currently migrating data from SAS datasets to a Microsoft SQL database. One of the users previously accessed a SAS table, performed analyses, added new columns, and then saved the output using the same table name. This worked in SAS because it could store the table in memory. However, this behavior doesn’t seem possible in Microsoft SQL, where the only apparent workaround is to save the output under a different name and then delete the old table—a time-consuming process that may result in users forgetting to remove the original table. Is there a more efficient way to handle this scenario?

[Kurt_Bremser · Accepted solution]

Have the users do their work in SAS with SAS datasets, and when the desired dataset is prepared, run PROC DELETE to remove the database table and create it anew.

[ballardw]

What you describe is generally considered poor practice in the world of relational databases. It is also possible that your current data design is poor if this sort of thing, adding variables to the same-named table, is needed.

A more typical approach would be to have a view or query that reads the existing data table that had the results needed and use the view for reporting or whatever that table with the additions is used for. Properly designed views would use "new" table after import or update or what ever is causing new data. Then nothing to delete, just remember to use the view instead of the raw data table.

[Sajid01]

Hello @mokwenak 
" However, this behavior doesn’t seem possible in Microsoft SQL....".
This behavior is possible in MS SQL Server but not in the same way as SAS. Here are some hints.

1.Once can always alter table and add /remove  columns

2.MS SQL server has the option to create session scope temporary table. At the end of the session the data is lost. Something like  SAS work in functionality . They can be used as an intermediate storage.

3.There is another feature called common table expressions which are named result set that can be withing the same query.

Ultimately it depends on your exact needs and the ability to make use of the available features.

[Patrick]

Tables in databases need stable structures. Unlike with SAS Files (tables) you normally also create the table structure only once and then insert, update or delete data attached to this structure. 

What you are describing feels like a bad use case for using SQL Server tables as "dumping ground" for SAS output.  It only complicates things for your users and likely also increases end-to-end processing time caused by frequent data transfers between SAS and the database.

What is the reason for pushing "all" SAS output to SQL Server?