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
The sample code on the Full Code tab shows how to code the DS2 SQLEXEC function to create a table and insert rows using the FedSQL CREATE and INSERT statements from within a DS2 program.
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.
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 |
Operating System and Release Information
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 | |