BookmarkSubscribeRSS Feed
NVK
Calcite | Level 5 NVK
Calcite | Level 5

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.

 

  
3 REPLIES 3
LinusH
Tourmaline | Level 20

Try the folowing optins, the databse might return something more to you...

options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;

 

Data never sleeps
Ksharp
Super User
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;
NVK
Calcite | Level 5 NVK
Calcite | Level 5

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.

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
  • 3 replies
  • 924 views
  • 0 likes
  • 3 in conversation