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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.