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]

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.