topic Re: SAS Access to Vertica - Insert from SAS Dataset using SQL pass-through facility in SAS Programming

SAS Access to Vertica - Insert from SAS Dataset using SQL pass-through facility

arpanshah — Tue, 15 May 2018 22:45:59 GMT

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.

Re: SAS Access to Vertica - Insert from SAS Dataset using SQL pass-through facility

ChrisNZ — Wed, 16 May 2018 02:43:55 GMT

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).

Re: SAS Access to Vertica - Insert from SAS Dataset using SQL pass-through facility

LinusH — Wed, 16 May 2018 02:50:59 GMT

Or PROC APPEND.
Or SQL INSERT INTO.
Or...etc

Re: SAS Access to Vertica - Insert from SAS Dataset using SQL pass-through facility

arpanshah — Wed, 16 May 2018 21:06:50 GMT

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.

Re: SAS Access to Vertica - Insert from SAS Dataset using SQL pass-through facility

ChrisNZ — Wed, 16 May 2018 21:23:03 GMT

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;

Re: SAS Access to Vertica - Insert from SAS Dataset using SQL pass-through facility

ChrisNZ — Wed, 16 May 2018 22:08:13 GMT

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;