I am testing the SAS/Access to Vertica Interface. I would like to know if there is a way to INSERT data into a Vertica table, from a SAS dataset, using the SQL pass-through facility or the LIBNAME option?
In my program,
- I have a SAS dataset stored on disk
- I would like to read that dataset and insert all the data into a table in Vertica
Can this be done? I would like SAS to create the table and insert all the rows. Do I have to pre-define the table definition or can SAS create it knowing Vertica data types and syntax? Is this even possible cause I haven't found any examples that do this.
Any support or pointers to code examples will be appreciated.
Oh sorry I misread. You want 2 columns in a new data base table?
Then this should work no?
proc sql; drop table VMART.TESTSAS; quit;
proc append base=VMART.TESTSAS data=DATADIR.APPTEST(keep=ID_EVNT ID_APPTEST); run;
I don't know Vertica, but assuming it is similar to other databases, standard SAS code should work.
data VERTLIB.TAB;
set SASLIB. TAB;
run;
proc copy should also work (and is preferable).
Thanks, but I will try to better articulate my problem here with code. I apologize but I am a beginner SAS developer:
Following is the simple SAS CODE:
libname datadir "~/data";
proc sql;
connect to vertica as vmart ( server="&rmtsrv" port=5433 database=verpoc user="&rmtuser" password="&rmtpw" INSERT_SQL=YES );
create table datadir.apptest as
select * from connection to vmart (select * from poc.apptest where extract (year from dttm_val) > 1582 limit 100);
disconnect from vmart;
***The above code created a sas dataset called apptest.sas7bdat in the data directory.
***I would now like to insert 2 columns from the apptest.sas7bdat into a new testsas table in the database.
proc sql;
connect to vertica as vmart ( server="&rmtsrv" port=5433 database=verpoc user="&rmtuser" password="&rmtpw" INSERT_SQL=YES );
select * from connection to vmart (insert into poc.testsas select id_evnt,id_apptest from datadir.apptest);
I know I cannot do what is highlighted in red because it executes on Vertica and that datadir.apptest is not valid.
I would like to know how I can Read rows from apptest.sas7bdat and insert them into Vertica and potentially insert the full sas apptest.sas7bdat dataset, after manipulation, back into Vertica. What is the right SQL pass-through syntax or alternative way to run the insert? Is there any bulk insert supported from the script?
Thanks.
You must do something like:
libname VMART vertica server="&rmtsrv" port=5433 database=verpoc user="&rmtuser" password="&rmtpw" INSERT_SQL=YES ;
proc append data=VMART.TESTSAS append=DATADIR.APPTEST;
run;
Oh sorry I misread. You want 2 columns in a new data base table?
Then this should work no?
proc sql; drop table VMART.TESTSAS; quit;
proc append base=VMART.TESTSAS data=DATADIR.APPTEST(keep=ID_EVNT ID_APPTEST); run;
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!
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.