Making changes to values in SQL Server table based on records in SAS table

holcoje — Mon, 21 Mar 2022 18:56:22 GMT

I have a SAS table that is a subset of records (10 records) from a SQL Server table (20,837 records). I need to update the value of a column in the SQL Server table, but only for the records that also exist in my aforementioned SAS table.

Specifically, if the record exists in the SAS table, then change the HISTORY_DATE value in the SQL Server table to today's date.

What would be the cleanest way to do this?

Re: Making changes to values in SQL Server table based on records in SAS table

SASKiwi — Mon, 21 Mar 2022 19:47:00 GMT

Is this going to be a regular process or is it just a one-off? If the number of records stays quite low then building a WHERE statement using a macro variable is probably easiest:

libname SQLSRVR odbc noprompt = "server=SQLServerName;DRIVER=SQL Server Native Client 11.0;Trusted Connection=yes" DATABASE = MyDatabase schema = dbo;

proc sql;
  select quote(Key_Var) 
  into :Key_Var_List separated by "," 
  from SAS_Table;
quit;

proc sql;
 connect using SQLSRVR;
  execute(
  update table Want
  Date_Var = '20220322'
  where Key_Var in (&Key_Var_List)
  ) by SQLSRVR;
quit;

topic Making changes to values in SQL Server table based on records in SAS table in SAS Programming

Making changes to values in SQL Server table based on records in SAS table

Re: Making changes to values in SQL Server table based on records in SAS table