topic Re: Updating a SQL table via ODBC from a SAS table in New SAS User

Updating a SQL table via ODBC from a SAS table

zdeb15 — Fri, 26 Jul 2019 14:20:39 GMT

Hi all, There is a table in our data warehouse that contains an e-mail distribution group. I want to update that table from SAS ...Is there a way to update a SQL table from a SAS table?

libname mylibref odbc datasrc=mydatasource schema=dbo;

proc Sql;
Connect to odbc (datasrc=mydatasource);

update [db].dbo.sqltable t1
set EmailList = (select EmailList
from SASemaillist1 t2
where t1.Team = t2.Team)
where team in (select t2.team from SASemaillist1 t2);

disconnect from odbc;

Re: Updating a SQL table via ODBC from a SAS table

Reeza — Fri, 26 Jul 2019 15:04:36 GMT

You're mixing explicit SQL and implicit SQL there. You cannot do that.

You need to find the SAS code that you need to run, not the SQL you're currently using and it should work directly from PROC EXPORT.

You cannot use explicit SQL here because it only passes your query to the server, not your SAS data.

libname mylibref odbc datasrc=mydatasource schema=dbo;

 

proc Sql;
Connect to odbc (datasrc=mydatasource); *makes this explicit SQL;

update [db].dbo.sqltable as t1
set EmailList = (select EmailList
from SASemaillist1 t2
where t1.Team = t2.Team)
where team in (select t2.team from SASemaillist1 t2);


disconnect from odbc;

Instead your code should look like the following - note I don't think this is correct and don't know what the correct code should be, just pointing you in the right direction.

proc sql;
update mylibref.sqltable as t1
set EmailList = (select EmailList
from SASemaillist1 t2
where t1.Team = t2.Team)
where team in (select t2.team from SASemaillist1 t2);
quit;

Re: Updating a SQL table via ODBC from a SAS table

zdeb15 — Mon, 29 Jul 2019 15:59:55 GMT

Here is my updated code with errors:

libname SQL odbc datasrc=myDSN schema=dbo;

proc Sql;
update SQL.EmailListingtest t1
  set EmailList = (select EmailList
   from SASemaillist1 t2
    where t1.Team = t2.Team)
     ,LastUpdateDt = (select LastUpdateDt
    from SASemaillist1 t2
     where t1.Team = t2.Team)
      where team in (select t2.team from SASemaillist1 t2);

I am getting these errors:

ERROR: Error binding parameters: [Microsoft][ODBC Driver 17 for SQL Server]Invalid precision value

ERROR: ROLLBACK issued due to errors for data set SQL.EmailListingtest.DATA.

Re: Updating a SQL table via ODBC from a SAS table

Reeza — Mon, 29 Jul 2019 16:04:04 GMT

Pretty sure that's now a SQL update error, which I don't know how to resolve. I would suggest trying a simpler query to ensure that updates are occurring and then once you know it can be done, fine tuning your SQL query to get it working as desired.