i found DB COMMIT option in the attached document which commits at regular interval in SAS
But still there is one problem, I work on Mainframe(Z/OS) and Mainframe(Z/OS) does not support DB commit option .so I cant use DB commit option for my problem.
Not sure if you requirement is suitable to breaking up your one PROC SQL into iterative subsets of your deletion requests, where you use a macro and you generate multiple PROC SQL executions.
Yes, the SAS Macro language is one option, although you may find sufficient capability by running a SAS DATA step to generate the PROC SQL executions out to a temporary sequential file (allocated with FILENAME) and then use %INCLUDE ; to invoke/execute your "generated" SAS code.
Hi Thanx for the information SSB . Macro is new concept to me.
Can u help me and plz expalin in detail what i need to do to genrate a Macro to commit at regualr intervals while deleting records from db2 tables.
For the time being , The job is executed successfully by writing the delete data to an output dataset and breaking the dataset into 5 smaller datasets I then ran the delete against each of the 5 new datasets
But More prominent solution is required as it is production job which runs frequently .
Please consider reading the suggested reference documentation or at least get started enough where you can learn and understand the SAS macro concept for repetitive code execution. Since you will need to support the code, it would be more suitable that you author the SAS application program to perform the process going forward. Others on this forum may be more about writing the code for you, however I prefer to guide others with learning SAS to accomplish tasks.
Good luck with your SAS programming experience using the macro facility.
The essence of the SAS macro facility is text substitution -- either at the statement level or the program level or at the single keyword or variable name level. Think of the SAS Macro Facility as a way to -generate- SAS code. The Macro facility, in and of itself, does not -execute- any code. It merely resolves macro variable references and macro program references into code which -can- be executed by the SAS compiler.
In addition to Scott's links and my introductory link, there are many, many previous postings in these forums and in SAS Global Forum papers and other user group papers -- everything from basic macro tutorials to examples of advanced macro applications.
There is no good way for anyone in the forum, to design, in detail or explain in detail the solution to your problem. You have to understand the macro facility a bit better in order to understand how it works and how you might apply usage of the Macro facility to solve your problem.
The paper I referenced at the beginning will show you the basics of how to start with a simple macro variable and will lead you through 9 steps until you understand how to write a simple macro program. The key to using the Macro facility is to start with a -WORKING- SAS program -- one that you know works and know that it needs to be run repetitively or one that needs to be generalized to run against differeing inputs (to name just a few of the reasons that folks use SAS macro programs). That way, you have a program that produces that desired results -- and against which, you can verify that your macro program is working correctly.