SAS files to DB2 Tables?

Reply
Frequent Contributor
Posts: 75

SAS files to DB2 Tables?

Hi, My objective is to read Excel file using SAS and populate the same in DB2 table. There is a DB2 table structure in place and the data processing needs to be done in SAS.  I found the following method in a documentation with the use of libname SAS access engine:

libname libref db2 db=name user=db2inst1 using=password;

data libref.db2_table (DBCOMMIT=10000);

set input_dataset;/*read from excel sheet*/

run;

Would the above translate to DB2 many datatypes properly as there are only 2 data types in SAS convention?

Is the above the only and most efficient way? Can anybody suggest other better ways?

Super User
Posts: 7,378

Re: SAS files to DB2 Tables?

Although I have no experience with direct write to DB/2, I would certainly not do it it one step, as that can be debugging hell.

Instead

1) Extract - read Excel file into SAS

2) Transform - cleanse the data in SAS (after all, this originated from Excel!), and set up for:

3) Load - transfer to DB/2

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 9,854

Re: SAS files to DB2 Tables?

Try  proc dbload  to fast it .

Frequent Contributor
Posts: 75

Re: SAS files to DB2 Tables?

Thank you, I will try. Can you point me to some nice and easy to read documentation besides what is there in support.sas.com?

Valued Guide
Posts: 3,208

Re: SAS files to DB2 Tables?

As there is a db2 table in place with the wanted variable structure you must be moe careful about dml and ddl the ddl is often reserved for the dba of the dbms. You

are only allowed to manipulate data that is add/drop records. This difference is often ignored by SAS programmers.

With proc append you can add records.

The data step will not work as the automatic deletion an replace of a table is not supported in a rdbms. Worse you will loose the predefined structure of the original dataset.

---->-- ja karman --<-----
Valued Guide
Posts: 3,208

Re: SAS files to DB2 Tables?

As there is a db2 table in place with the wanted variable structure you must be moe careful about dml and ddl the ddl is often reserved for the dba of the dbms. You

are only allowed to manipulate data that is add/drop records. This difference is often ignored by SAS programmers.

With proc append you can add records.

The data step will not work as the automatic deletion an replace of a table is not supported in a rdbms. Worse you will loose the predefined structure of the original dataset.

---->-- ja karman --<-----
Frequent Contributor
Posts: 75

Re: SAS files to DB2 Tables?

Wow, Nice one. I forgot the Proc append. Cheers Jaap!

Ask a Question
Discussion stats
  • 6 replies
  • 240 views
  • 0 likes
  • 4 in conversation