topic Re: Error loading to netezza table in SAS Programming

Error loading to netezza table

kajal_30 — Fri, 03 Jun 2022 14:46:45 GMT

I am trying importing one excel file into sas table and then creating temporary dataset adding 2 new columns target_load_ts and yearmonth. but while loading the netezza table from the work table getting CLI execute error. error: Relation does not exist nzdb.pdit.temp2.

PROC IMPORT 
		OUT= TEMP1 
		DATAFILE= "/sas/test/files/&in_file..xlsx" 
		DBMS=xlsx 
		REPLACE;
		GETNAMES=YES;
	RUN;

	DATA  TEMP2;
		SET  TEMP1;
		TARGET_LOAD_TS = datetime();
		yearmonth = &date_er.;
		FORMAT TARGET_LOAD_TS datetime20.;
	RUN;

	proc sql;
		connect to &NZCONN.;
		Execute(INSERT INTO &out_table select * from TEMP2
			) by NETEZZA;

		%setNZRC(&SQLRC);
		Disconnect from NETEZZA;
	quit;

Re: Error loading to netezza table

ChrisNZ — Sat, 04 Jun 2022 06:57:26 GMT

Execute ( INSERT INTO &out_table select * from TEMP2 ) by NETEZZA;

The bit in blue executes in NETEZZA right?

Do you have a table called TEMP2 in NETEZZA?

Since you don't, you need to upload it.

If you can't do that, just run something like a proc append and SAS will upload the data for you.

Re: Error loading to netezza table

Kurt_Bremser — Sat, 04 Jun 2022 09:42:41 GMT

This can never work.

TEMP2 is a dataset in the SAS WORK library, but everything within the parentheses

Execute(INSERT INTO &out_table select * from TEMP2
			) by NETEZZA;

is executed solely within the DB, which does not have any connection to your SAS WORK. The EXECUTE code could only work if TEMP2 was uploaded first to Netezza.

Re: Error loading to netezza table

kajal_30 — Sat, 04 Jun 2022 20:13:34 GMT

yes temp2 itself is my final table which I want to load to Netezza. Is there a way I can upload it to Netezza as I already have the table metadata in the Netezza but with a different name.

Re: Error loading to netezza table

Tom — Sat, 04 Jun 2022 21:46:52 GMT

Try creating a libref using the LIBNAME statement instead of using the CONNECT statement of PROC SQL.

So say you defined the libref NETZ and there is a table there named FRED you could try to load the data from WORK.TEMP2 by using PROC APPEND code like this:

proc append base=NETZ.FRED data=TEMP2;
run;

Re: Error loading to netezza table

kajal_30 — Sun, 05 Jun 2022 06:46:21 GMT

Hi Tom,

Thank you so much. At first sight it seems like it worked but at the end I saw weird results log says

NOTE: There were 1 observations read from the data set WORK2.TEMP2.
NOTE: 1 observations added.
NOTE: The data set NZlib.abc has . observations and 2 variables.

Please advise

Thanks

Jagdeep

Re: Error loading to netezza table

Kurt_Bremser — Sun, 05 Jun 2022 09:46:16 GMT

@kajal_30 wrote:

Hi Tom,

Thank you so much. At first sight it seems like it worked but at the end I saw weird results log says

NOTE: There were 1 observations read from the data set WORK2.TEMP2.
NOTE: 1 observations added.
NOTE: The data set NZlib.abc has . observations and 2 variables.

Please advise

Thanks

Jagdeep

Looks like the append worked, but Netezza does not return a proper row number.

Inspect the target dataset in the DB to see if the append really worked as intended.