simulating cursor logic in SAS

[9776]

Friends, I am fairly experienced in SAS but am finding hard to find a better way to handle the below problem in SAS.

I have a table with multiple fields. eg. Policy table with fields Pol_ref,Date,Premium fields. Pol_ref is not unique. First I need to check if the Pol_ref is within inputted date range. Once I get the Pol_ref, I will extract other fields from the table.

Currently it is done by having a subselect (applying date criteria) in the query but it is consuming lot of time as the table is massive. Also I cannot apply the date criteria on the main query because I will get latest record where as I want oldest record.

I can read all the Pol_refs into a SAS table but I am not sure how I can pass each Pol_ref to the where clause in the DB2 query. I think the option of reading the main table into a Sas dataset and joining both tables can be ruled out as the main table is quite large.

I gave subject as 'cursor logic' because this can be thought as first reading a lookup table into Sas and for each record processing a large table.

Please provide your thoughts on this.

Thanks alot!!!

 

[klaz2002]

9776,
It looks like you are using a DB2 database. Well why not write a stored proc on the DB2 side that will do your where logic. This is always faster than executing via a SAS data/proc step. You could build your proc to accept params and do your "look-up" for you. Your SAS program will only get the records it needs to do its analysis. Here is a link to an article on how to write the stored proc in DB2.

http://www.devx.com/IBMDB2/Article/28308

You can use the SAS SQL PassThrough facility in SAS to execute and get the results. As I wrote before its always faster to have the database crunch the where clause than SAS.

Klaz

 

[jymm]

I have done similar things on MS SQL side with Views & Stored Procedures. The Views coupled with some index work really made those large tables whip out quickly --- sorry I do not know the same terms in DB2.

Experience on the MS SQL side says stay away from cursors as much as possible - you can usually make it work with joins, temp tables and views so that it will work much quicker than touching each record with a cursor - my 2 cents.

 

[9776]

klaz2002 and jymm, thanks for your prompt responses. Unfortunately I won't be able to change the existing process and introduce stored proc on DB2 side. The existing program does lot of complex processing. So we are only interested in improving the performance by removing the subselect (to get list of pol_refs matching the date criteria).

I can think of only 2 options - 1. Using FORMATS - create a format with all pol_ref values and use this format in the main query. 2. read the pol_ref (with date criteria applied) and the DB2 table into Sas dataset (i am a bit suspiscious because the DB2 table is quite large) and join the small (lookup) table with the large (master) table.

However I don't know which is the better option and also how complex (coding) the options are.

Please suggest. Thanks.