topic Re: PROC SQL create specific data type and insert data to table in New SAS User

PROC SQL create specific data type and insert data to table

Thanakid — Fri, 08 Mar 2019 10:24:24 GMT

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;

Re: PROC SQL create specific data type and insert data to table

andreas_lds — Fri, 08 Mar 2019 09:14:19 GMT

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?

Re: PROC SQL create specific data type and insert data to table

Thanakid — Fri, 08 Mar 2019 10:24:41 GMT

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.

Re: PROC SQL create specific data type and insert data to table

SASKiwi — Fri, 08 Mar 2019 10:05:26 GMT

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.

Re: PROC SQL create specific data type and insert data to table

RW9 — Fri, 08 Mar 2019 10:56:58 GMT

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.

Re: PROC SQL create specific data type and insert data to table

SASKiwi — Fri, 08 Mar 2019 21:52:16 GMT

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.

Re: PROC SQL create specific data type and insert data to table

Thanakid — Mon, 11 Mar 2019 02:53:05 GMT

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.

Re: PROC SQL create specific data type and insert data to table

Thanakid — Mon, 11 Mar 2019 05:16:25 GMT

I follow this suggestion, it working!!

Re: PROC SQL create specific data type and insert data to table

Thanakid — Mon, 11 Mar 2019 06:35:07 GMT

This is solution