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

[Tater_Salad]

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!

[SASKiwi]

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;

[Tater_Salad]

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.

[Reeza]

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

[Tater_Salad]

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.

[Patrick]

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;

[Tater_Salad]

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

[Patrick · Accepted solution]

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

[Tater_Salad]

NOICE! 

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

[Tater_Salad]

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?

[Tater_Salad]

Never Mind I figured it out.

[Patrick]

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