I am trying to insert records from SAS table into Amazon RDS using ODBC libname statement. My log shows that records have been inserted , however the select query returns null. I tried commit after insert, but it is still not working.
When i try inserting from sql prompt it works.
I am trying a normal proc sql statement .
Code :-
libname test1 odbc user=XX password="XX" datasrc="Amazon RDS ODBC DSN" autocommit=yes dbcommit =1000; proc sql; insert into test1.tmp_list (cust_iden, process_id) values (1,10); select * from test1.tmp_list; quit;
Log
*NOTE: Libref TEST1 was successfully assigned as follows: Engine: ODBC Physical Name: Amazon RDS ODBC DSN 3 proc sql; 4 insert into test1.tmp_list (cust_iden, process_id) values (1,10); NOTE: 1 row was inserted into TEST1.tmp_list. 5 select * from test1.tmp_list; NOTE: No rows were selected. 6 7 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.09 seconds cpu time 0.05 seconds*
There is no error displayed in the log which indicates a rollback. Where is my record disappearing? I want to be able load a list of cust_iden's from a sas dataset into this temp tables.
I am getting the same result even when the select query is in a different proc. I also logged into the database server, but none of the records have been added to the table
Any pointers would be helpful. Thanks.
Try the folowing optins, the databse might return something more to you...
options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;
Maybe you need reconnect to libname . libname test1 odbc user=XX password="XX" datasrc="Amazon RDS ODBC DSN" autocommit=yes dbcommit =1000; proc sql; insert into test1.tmp_list (cust_iden, process_id) values (1,10); quit; libname test1 clear; libname test1 ...... proc sql; select * from test1.tmp_list; quit;
The libname clear command is not working.
I am checking with sastrace option to see what is happening at database end. Will put in the findings soon.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.