DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

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

[ Edited ]

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. 


Accepted Solutions
Solution
‎05-17-2018 10:53 AM
Super User
Posts: 2,492

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

Posted in reply to arpanshah

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;

 

View solution in original post


All Replies
Super User
Posts: 2,492

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

Posted in reply to arpanshah

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

New Contributor
Posts: 2

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

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. 

 

Super User
Posts: 2,492

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

Posted in reply to arpanshah

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;

 

Solution
‎05-17-2018 10:53 AM
Super User
Posts: 2,492

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

Posted in reply to arpanshah

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;

 

Super User
Posts: 5,914

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

Posted in reply to arpanshah
Or PROC APPEND.
Or SQL INSERT INTO.
Or...etc
Data never sleeps
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 149 views
  • 1 like
  • 3 in conversation