50962 - How to use the DS2 SQLEXEC function to create a table and insert rows using the FedSQL CREATE and INSERT statements from within a DS2 program

Sample 50962: How to use the DS2 SQLEXEC function to create a table and insert rows using the FedSQL CREATE and INSERT statements from within a DS2 program

In this example, a table called TESTDATA is created and observations from the data set called SAMPLE_DATA are inserted into TESTDATA.

Code for creating the table is done within the INIT() method, as the creation occurs only once. In this sample, the name of the table is also coded in the INIT() method. Be sure to use a RETAIN statement for the variable containing the name of the table [tablename]. Otherwise, it will be reset to missing after the first iteration of the RUN() method.

Code for inserting rows into the table is done within the RUN() method as observations are sequentially read from the data set called SAMPLE_DATA. In this sample, the generated INSERT statement needs to be similar to:

insert into testdata values('John Smith',30)

To generate the single quotation marks around 'John Smith', three single quotation marks must be coded.

data sample_data;
   length name $40;
   name = 'John Smith'; age = 30; output;
   name = 'Jane Doe'; age = 25; output;
run;

/* You might want to first delete the table created in the DS2 program if it already exists. */
proc datasets library=work nolist;
   delete testdata;
quit;

proc ds2;
   data _NULL_;
      dcl varchar(8) tablename;
      retain tablename;
      dcl char(40) name;
      dcl double age;
      dcl varchar(180) s;
      method init();
/* Create the table. */
         tablename = 'testdata';
         s = 'create table ' || tablename || '(name char(40), age double)';
         sqlexec(s);
      end;
      method run();
/* Insert rows from the sample table. */
         set sample_data;
         s = 'insert into ' || tablename || ' values(''' || trim(name) || ''',' || age ||')';
         sqlexec(s);
      end;
   enddata;
run;
quit;

proc print data=testdata;
run;

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Obs NAME AGE 
1 John Smith 30 
2 Jane Doe 25

Date Modified:	2013-11-13 15:57:49
Date Created:	2013-09-04 16:14:55

Product Family	Product	Host	SAS Release
Product Family	Product	Host	Starting	Ending
SAS System	N/A	DB2 Universal Database on AIX	9.4 TS1M0
		DB2 Universal Database on Linux x64	9.4 TS1M0
		Greenplum on Linux x64	9.4 TS1M0
		Netezza TwinFin 32bit blade	9.4 TS1M0
		Netezza TwinFin 32-bit SMP Hosts	9.4 TS1M0
		Teradata on Linux	9.4 TS1M0
		z/OS	9.4 TS1M0
		Z64	9.4 TS1M0
		Apple Mobile Operating System	9.4 TS1M0
		Microsoft® Windows® for 64-Bit Itanium-based Systems	9.4 TS1M0
		Microsoft® Windows® for x64	9.4 TS1M0
		Microsoft Windows 8 Enterprise 32-bit	9.4 TS1M0
		Microsoft Windows 8 Enterprise x64	9.4 TS1M0
		Microsoft Windows 8 Pro 32-bit	9.4 TS1M0
		Microsoft Windows 8 Pro x64	9.4 TS1M0
		Microsoft Windows Server 2008 R2	9.4 TS1M0
		Microsoft Windows Server 2008 for x64	9.4 TS1M0
		Microsoft Windows Server 2012 Datacenter	9.4 TS1M0
		Microsoft Windows Server 2012 Std	9.4 TS1M0
		Windows 7 Enterprise x64	9.4 TS1M0
		Windows 7 Professional x64	9.4 TS1M0
		64-bit Enabled AIX	9.4 TS1M0
		64-bit Enabled HP-UX	9.4 TS1M0
		64-bit Enabled Solaris	9.4 TS1M0
		HP-UX IPF	9.4 TS1M0
		Linux	9.4 TS1M0
		Linux for x64	9.4 TS1M0
		Solaris for x64	9.4 TS1M0

Support

Sample 50962: How to use the DS2 SQLEXEC function to create a table and insert rows using the FedSQL CREATE and INSERT statements from within a DS2 program

Operating System and Release Information