How to Load Data into DB2 tables using SAS Macros?

[Krisan]

Hi,

I have 10 files of different type (like csv, xls etc) with a different layout. My requirement is to read and load these files into different tables in DB2 Database. The table structure needs to be defined by us based on the file layout.

Also, the database needs to be refreshed on a weekly basis such that if a new record comes in any of these files, we need to append it to the corresponding existing tables.

How can we achieve this? Is it possible to write a SAS Macro to perform this load and refresh process in DB2?

Regards,

Krisan

[DanielSantos]

I don't see any need to use macros for loading data into DB2.

If you want to load data into DB2 directly from SAS you must first ensure you have SAS/ACCESS for DB2 product licensed for your site.

Then loading is just a matter of choosing one of the few techniques available.

Using the SAS/ACCESS library engine for DB2:

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001342619.htm

for a simple and transparent access to the DBMS (DB2 tables will actually be available "as if" they were SAS tables through the library connected to the DBMS).

Or if you care for performance, specifying the BULKLOAD option, which is just the fastest method to put data from SAS to DB2 (replace or resume).

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

[Patrick]

The SAS client for ETL development would be SAS Data Integration Studio. There you would have ready made loaders.

You can write your own loader and implement it as a SAS macro. First  write the code for loading one of the tables and test it. Only then generalize it by wrapping a macro around.

As you need to load deltas you will need to somehow compare your new source records with the already loaded target records.

There are different ways of doing this and it depends on what volumes you're dealing with. What you want to avoid is loading the target table into SAS for doing this comparison.

May be easiest when "hand coding" would be to load the business keys from source into the db, compare with the db table and create a table with the new keys ( the delta). You then use this delta within SAS to select your new records and you append them to the target table via Proc Append. If there are a lot of records to be loade then you can define bulk load options with Proc Append.

If you want to use such an approach then this link will be of interest for you http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a002680719.htm