BookmarkSubscribeRSS Feed
UrvishShah
Fluorite | Level 6

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

1 REPLY 1
jakarman
Barite | Level 11

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 --<-----

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 2106 views
  • 0 likes
  • 2 in conversation