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