KEY = Option in MODIFY Statement to update DBMS Table

Reply
Regular Contributor
Posts: 195

KEY = Option in MODIFY Statement to update DBMS Table

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

Valued Guide
Posts: 3,208

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

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

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 1 reply
  • 435 views
  • 0 likes
  • 2 in conversation