avoiding subselects

[9776]

Friends,
I think my earlier post went deviated from my expectations and I would to rephrase the question in simple terms.

I have a table with large number of rows. The fields are Policy_num, Eff_date, ...

For an input date range (Eff_date), I need to extract the Policy_nums. After that I need to select the records with these Policy_nums. I cannot apply the date criteria on whole table as it gives me the records with latest Eff_date where as I need the records with oldest Eff_date.

Currenly I did it as a DB2 SQL subselect but it is consuming lot of CPU to run. I am not sure of DB2 stored procedures. So I am just looking for ways to implement in SAS.

Please help.

Thanks.

 

[ChrisW75]

You can do it as 2 steps in SAS, would consume less CPU, but more disk space while processing.
You take a set of records for the date range. Dedupe it to get just a list of the policy numbers, then join that back.
An alternative, which is faster if you're only dealing with a few thousand policy numbers, but a little heavier on the CPU, is to convert this list of policy numbers into a format, and use the format on the original data, then use the formated value to do your selection.
If you want it spelled out a little more, let me know.