SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
OGA
Fluorite | Level 6 OGA
Fluorite | Level 6

Hi

I am trying to create a table in a Hive database reading data from a different Hive database. If I do it with implicit sql, it works:


OPTION SASTRACE=',,,ds' SASTRACELOC=SASLOG NOSTSUFFIX SQLIPONEATTEMPT SQL_IP_TRACE=(note, source) msglevel=i;
proc sql;

create table hvneglab.ci0101_tablaneglab_sas4 as
select ci0101_numpersona
from hvnego.ci0101
where ci0101_nroperiod eq 2003
and meta_codienti = '2095';

quit;
OPTIONS SASTRACE=off;

 

But if I try to do it with Explicit SQL pass-through I am not able to make it work. This is the way I am doing it:

proc sql;

connect using HVNEGLAB as A;
connect using HVNEGO as B;
select * from connection to A(

create table ci0101_tablaneglab_sas3 as
select * from connection to B(

select ci0101_numpersona
from ci0101
where ci0101_nroperiod eq 2003
and meta_codienti = '2095'));

execute(disconnect from B) by A;
disconnect from A;

quit;

 

The first 2 lines need to remain as they are, since users are not allowed to create a connection to a database themselves. They can only do it by using one of the pre-assigned libraries they have available: hvneglab o hvnego

The error I get is:

ERROR: Prepare error: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: ParseException line
1:65 cannot recognize input near 'connection' 'to' 'B' in from source
SQL statement: create table ci0101_tablaneglab_sas3 as select * from connection to B( select ci0101_numpersona from ci0101 where
ci0101_nroperiod eq 2003 and meta_codienti = '2095')

 

Could anyone advise how to do it?

Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

Either you connect to A or to B.

In the first piece of code, SAS reads the data from B and then writes it to A.

In the second piece, SAS does nothing. The code in blue is run by Hadoop.

proc sql;

connect using HVNEGLAB as A;
connect using HVNEGO as B;
execute by A (

create table ci0101_tablaneglab_sas3 as
select * from connection to B(

select ci0101_numpersona
from ci0101
where ci0101_nroperiod eq 2003
and meta_codienti = '2095') );

execute(disconnect from B) by A;
disconnect from A;

quit;

SAS has no idea what the blue code means.

And Hadoop has no idea what B is.

The blue code is Hadoop code. You need a Hadoop reference there.

Ask the Hadoop people. Maybe something like:

execute by A (

create table ci0101_tablaneglab_sas3 as
select ci0101_numpersona
from hvnego.ci0101
where ci0101_nroperiod eq 2003
and meta_codienti = '2095' );

Note that since you have a create table in your Hadoop code, SAS retrieves no data.

So you must use execute by, not select from: You are not selecting anything.

 

 

 

 

View solution in original post

2 REPLIES 2
ChrisNZ
Tourmaline | Level 20

Either you connect to A or to B.

In the first piece of code, SAS reads the data from B and then writes it to A.

In the second piece, SAS does nothing. The code in blue is run by Hadoop.

proc sql;

connect using HVNEGLAB as A;
connect using HVNEGO as B;
execute by A (

create table ci0101_tablaneglab_sas3 as
select * from connection to B(

select ci0101_numpersona
from ci0101
where ci0101_nroperiod eq 2003
and meta_codienti = '2095') );

execute(disconnect from B) by A;
disconnect from A;

quit;

SAS has no idea what the blue code means.

And Hadoop has no idea what B is.

The blue code is Hadoop code. You need a Hadoop reference there.

Ask the Hadoop people. Maybe something like:

execute by A (

create table ci0101_tablaneglab_sas3 as
select ci0101_numpersona
from hvnego.ci0101
where ci0101_nroperiod eq 2003
and meta_codienti = '2095' );

Note that since you have a create table in your Hadoop code, SAS retrieves no data.

So you must use execute by, not select from: You are not selecting anything.

 

 

 

 

sas-innovate-white.png

Special offer for SAS Communities members

Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.

 

View the full agenda.

Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 3078 views
  • 1 like
  • 2 in conversation