KEY = Option in MODIFY Statement to update DBMS Table

UrvishShah — Mon, 04 Aug 2014 08:54:28 GMT

Hi,

I tried to update the DBMS Table by using following LIBNAME statement...based on my knowledge if we want to user KEY= option on DBMS Table then reread_exposure=yes must be set...In below example, Index is already created on PK variable, so i can directly use it in KEY= option...

my_data.test contains one record and i need to update the table based on following data...

But i am getting following error...

ERROR: No key variables have been defined for file test.

ERROR: ROLLBACK issued due to errors for data set TEST.test.DATA.

LIBNAME my_data MYSQL SERVER="xxxxx"

DATABASE=my_database MYSQL_PORT=3306 USER=xxxx

PASSWORD="xxxxxxxxx" reread_exposure=yes;

data my_data;

input pk var;

cards;

0 6

1 7

2 8

;

data my_data.test;

set my_data(rename=(var = new_var));

modify my_data.test

key = pk; /* In our case, Index is already created for SK, so we can use it */

if _IORC_ = %sysrc(_sok) then do;

var = new_var;

replace;

end;

else if _IORC_ = %sysrc(_dsenom) then do;

var = new_var;

output;

_ERROR_ = 0;

end;

run;

Can anyone help me why i am getting this error eventhough i set the proper options...

-Urvish

Re: KEY = Option in MODIFY Statement to update DBMS Table

jakarman — Mon, 04 Aug 2014 12:55:44 GMT

The modify is not often used. The message is coming from your DBMS system.

It says there have no indexes been defined within the DBMS on your table so key= is failing.

the sastrace option (and more like that) should help in debugging. http://www2.sas.com/proceedings/sugi31/264-31.pdf

topic KEY = Option in MODIFY Statement to update DBMS Table in SAS Programming

KEY = Option in MODIFY Statement to update DBMS Table

Re: KEY = Option in MODIFY Statement to update DBMS Table