Working with Amazon
RDS and SAS/ACCESS®
Amazon RDS is a service offered by Amazon.com that allows a user to build and operate a MySQL, Oracle, PostgreSQL, Microsoft SQL Server, or Amazon Aurora database without the need to maintain their own hardware and without the need of a DBA to set it up. Database behavior running on Amazon RDS is no different than an equivalent on-premises deployment of a given database. Amazon is running an off-the-shelf product, so once connectivity is established there will be no noticeable difference in how SAS/ACCESS behaves. This article provides some tips on configuring ACCESS to connect with an Amazon RDS database instance using an Oracle as an example.
Prior to establishing connectivity, you will need to set up a database instance with a userid and password to connect. Amazon has GettingStarted
documentation that walks through this process.
Once you have a hostname and port, connecting to the instance is no different than connecting to any Oracle database.
Start by verifying that the database host can be accessed. On Linux, this can be accomplished with the following example command:
nc
–z mk-oracle-11-al32utf8-test.cyydrdghsaxs.us-east-1.rds.amazonaws.com 1521
Edit the tnsnames.ora file on the client to create a reference to the database instance. For example:
test_ora=
(DESCRIPTION =
(ADDRESS = (PROTOCOL= TCP)(Host=mk-oracle-11-al32utf8-test.cyydrdghsaxs.us-east-1.rds.amazonaws.com)(Port=1521))
(CONNECT_DATA = (SID = ORCL))
)
Open a DOS/UNIX session and try to query the database using SQLPlus or a database query tool of your choice. If necessary, set TNS_ADMIN={Oracle tnsnames.ora directory path}. For example:
sqlplus admin/pocadmin@test_ora
You should be ready to proceed with testing. Below are some common actions that can be performed along with some example syntax to get you started. LIBNAME libname
x oracle user=admin password=pocadmin path=test_ora; Table creation and query
input
test_string $25.; datalines; Bugs Bunny Homer Simpson; run; proc sql; create
table x.create_test asselect
* from x.test_tbl; quit; data
_null_; set x.create_test; put _all_; run; Bulk Loadingdata sasds; c=1;run; proc sql; drop
table ora.bulktest1;quit;
set sasds;
run;
proc print data=ora.bulktest1;
run;
Insert
proc sql;
insert into x.test_tbl
set test_string='Fred Flintstone';
proc sql;
insert into x.test_tbl
values ('Barney Rubble');
Update
proc sql;
update x.test_tbl
set test_string='George Jetson'
where test_string='Bugs Bunny';
data _null_; set x.test_tbl; put _all_; run;
proc sql;
update x.test_tbl
set test_string='George Jetson'
where test_string='Bugs Bunny';
data _null_; set x.test_tbl; put _all_; run;
Delete
proc sql;
delete
from x.test_tbl
where test_string like 'F%';
data _null_; set x.test_tbl; put _all_;
There’s not much to it than that. As you can see once an Amazon RDS db is set up it is handled the same as any other db. Please share your comments and questions on this article.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning and boost your career prospects.