Error loading to netezza table

[kajal_30]

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;

[ChrisNZ]

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.

[Kurt_Bremser]

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.

[kajal_30]

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. 

[Tom]

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;

[kajal_30]

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

[Kurt_Bremser]

---

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