topic Re: SAS: Want to know how to pass data from a WORK.WHATEVER table into a Netezza table in SAS Programming

SAS: Want to know how to pass data from a WORK.WHATEVER table into a Netezza table

Tater_Salad — Tue, 23 Oct 2018 19:47:48 GMT

Hi,

Using SAS V 9.xxx.

How do I pass data from a table in the generic WORK library to a table in Netezza.

Right now I can take data out of Netezza and store the WORK library, for example:

/* using “connect to” */

proc sql;
connect to netezza (&us_mkt.); 
execute (

drop table &tempdir..SALES_TBL_TST1; 

create table &tempdir..SALES_TBL_TST1
as
select * from &tempdir..SALES_TBL;
/*select * from &tempdir..SALES_TBL */

) by netezza;
disconnect from netezza;
quit;

/* using “connect using” */

proc sql;

connect using ROLAP;

drop table WORK.SALES_TBL_TST2;

create table WORK.SALES_TBL_TST2 AS
select * from connection to ROLAP
(select * from USER_ROLAP.SALES_TBL);

insert into WORK.SALES_TBL_TST2
select * from connection to ROLAP
(select * from &tempdir..SALES_TBL);

quit;

But what if I want to put data back into Netezza from the WORK library. So for example:

insert into &tempdir..SALES_TBL_TST1 
select * from WORK.SALES_TBL_TST2;

Is there some way to do this?

Thanks in Advance!

Re: SAS: Want to know how to pass data from a WORK.WHATEVER table into a Netezza table

SASKiwi — Tue, 23 Oct 2018 20:56:49 GMT

I suggest you try loading a SAS table as a Netezza temporary table, then you can use INSERT INTO from the temporary table. The easiest way to load a temporary table is to assign a LIBNAME statement accessing Netezza:

libname netload netezza &us_mkt. ; * Might need to tweak your connection options;

data netload.sales_tbl_test;
  set work.sales_tbl_test;
run;

Re: SAS: Want to know how to pass data from a WORK.WHATEVER table into a Netezza table

Reeza — Tue, 23 Oct 2018 21:01:09 GMT

Right now you're using SQL Pass through. Can you connect in a different manner, specifically a libname reference?

Re: SAS: Want to know how to pass data from a WORK.WHATEVER table into a Netezza table

Patrick — Tue, 23 Oct 2018 21:07:33 GMT

You need a libname statement pointing to Netezza. If inserting data into an existing table then Proc Append is an option.

libname netload netezza &us_mkt. ; * Might need to tweak your connection options;

proc append base=netload.sales_tbl_test data=work.sales_tbl_test;
run;

Re: SAS: Want to know how to pass data from a WORK.WHATEVER table into a Netezza table

Tater_Salad — Tue, 23 Oct 2018 21:48:55 GMT

Yup, I did that one:

data ROLAP.SALES_TBL_TST4;
    set WORK.SALES_TBL;

run;

Probably the quickest way to load data from SAS up to Neteeza.

But you have to load the whole table, it looks like. No more, no less.

Definitely one super-easy way.

Re: SAS: Want to know how to pass data from a WORK.WHATEVER table into a Netezza table

Tater_Salad — Tue, 23 Oct 2018 21:50:23 GMT

Yup, that's what they have me doing. I suspected libraries would be part of the solution but being new to SAS I didn't know their range.

Re: SAS: Want to know how to pass data from a WORK.WHATEVER table into a Netezza table

Tater_Salad — Tue, 23 Oct 2018 22:03:35 GMT

Ok, so it looks like this is an alternative to

data ROLAP.SALES_TBL_TST4;
set WORK.SALES_TBL;

run;

Only in this instance your adding data. It looks like you're taking everything from the source table and inserting it into netload.sales_tbl_test.

Is there a way to do a custom query as part of your install. Something complicated.

insert into netload.sales_tbl_test 
  select a.c1, a.c2.... from work.table1 a, work.table2  b 
 where a.c1 = b.c1 and a.c2=b.c2 etc ,...

Something like that..

Re: SAS: Want to know how to pass data from a WORK.WHATEVER table into a Netezza table

Patrick — Wed, 24 Oct 2018 07:54:32 GMT

@Tater_Salad

Code like below should do.

libname netload (work); /* here your actual libname pointing to Netezza */

data work.source1;
  varA='source1';
  do key=1 to 5;
    output;
  end;
  stop;
run;

data work.source2;
  varB='source2';
  do key=2 to 6;
    output;
  end;
  stop;
run;

data netload.target;
  length key 8 VarA VarB $7;
  stop;
run;

/* option 1 */
proc sql;
  insert into netload.target 
    select 
      a.key,
      a.varA,
      b.varB
    from 
      work.source1 as a, work.source2 as b
      where a.key=b.key
    ;
quit;


/* option 2 */
proc sql;
  create view work.Vsource as
    select 
      a.key,
      a.varA,
      b.varB
    from 
      work.source1 as a, work.source2 as b
      where a.key=b.key
    ;
quit;

proc append base=netload.target data=work.vSource;
run;

I personally would likely go for option 2 as this makes it easier to use stuff like bulkload or fastload (or whatever Netezza offers).

Re: SAS: Want to know how to pass data from a WORK.WHATEVER table into a Netezza table

Tater_Salad — Wed, 24 Oct 2018 14:35:37 GMT

NOICE!

** NOTE ** Everyone else's solution was correct as well. This one just took me the farthest.

Re: SAS: Want to know how to pass data from a WORK.WHATEVER table into a Netezza table

Tater_Salad — Wed, 24 Oct 2018 16:24:49 GMT

OK, I have one question about the following:

data netload.target;
  length key 8 VarA VarB $7;
  stop;
run;

Would it be possible to just say:

data netload.target;
  stop;
run;

Assuming the columns will define themselves as I insert the data into the target. Like the SQL statement:

create TABLE work.Vsource as
    select 
      a.key,
      a.varA,
      b.varB
    from 
      work.source1 as a, work.source2 as b
      where a.key=b.key
    ;

The way a table you're creating from a query figures out the columns by itself? Can I just do that?

Re: SAS: Want to know how to pass data from a WORK.WHATEVER table into a Netezza table

Tater_Salad — Wed, 24 Oct 2018 20:50:51 GMT

Never Mind I figured it out.

Re: SAS: Want to know how to pass data from a WORK.WHATEVER table into a Netezza table

Patrick — Wed, 24 Oct 2018 23:18:39 GMT

@Tater_Salad

I normally prefer to explicitly define table structures in a data base using pass-through SQL. You can SAS have doing it implicitly but it gives you less control.