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!
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).
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;
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.
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.
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;
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..
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).
NOICE!
** NOTE ** Everyone else's solution was correct as well. This one just took me the farthest.
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?
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.