PROC SQL create specific data type and insert data to table

[Thanakid]

Hi,

I am newbie for SAS EG, May I need you guy help me about issue,

I would like to create the table with specific the data type, then insert the data as select from once table use PROC SQL with Neteza DB,

The result create table is working but NOT any data insert to that new table as I have created, my code as below,

PROC SQL;
CONNECT TO NETEZZA AS CON1(DATABASE=SAS_ADM SERVER="192.168.00.00" AUTHDOMAIN= "ABC");
EXECUTE(

		
		CREATE TABLE SAS_ADM..TK_TEMP3 
                ( 
                      Center        NATIONAL CHARACTER VARYING(10),
                      FoundSentence NATIONAL CHARACTER VARYING(255)
                );

  	    INSERT INTO SAS_ADM..TK_TEMP3
		SELECT Center, 
                FoundSentence
 	    FROM WORK.FILE_TK;
		
		) BY CON1;
		DISCONNECT FROM CON1;
QUIT; 

[andreas_lds]

Please note that a single dot is used to separate library from dataset. And if you want to load data from Netezza, why do have "from work.file_tk" in your sql-statement?

[Thanakid]

I have import the csv file and store in WORK library, for move to Neteza libray in next step.

I have change to single dot in my statement but result still same, NOT any data have insert to new table.

[SASKiwi]

You are passing an SQL query directly to Netezza to process - it knows nothing about SAS WORK datasets. You have to move the SAS dataset to Netezza before loading it.

A better way to load the data would be to create a Netezza connection with a LIBNAME statement and then use PROC DATASETS with an APPEND statement to insert the SAS rows into the Netezza table.

[RW9]

Would a better process not be to import the CSV directly into Netezza?  Having an intermediary stage of loading to one software just to pass to another is adding an unnecessary layer of complexity and potential issues.

[SASKiwi · Accepted solution]

The method in SAS is pretty simple:

libname netezza netezza noprompt="<put your connection string here>";

proc datasets library = netezza;
  append base = netezza.MyTable data = work.MySASTable;
run;
quit; 

 If you want a Netezza-only solution then I suggest you use a Netezza forum.

[Thanakid]

I follow this suggestion, it working!!

[Thanakid]

This is solution

[Thanakid]

I'am user in my company, I have limitation access to environment Netteza and i know this solution to prepare CSV data into Netteza first and read completed table via SAS EG, that why I import the CSV file via SAS EG only. maybe I will try to talk with person who responsibility with Netteza for import this CSV file.