topic Re: Optimization of processing huge data sets in SAS Programming

Optimization of processing huge data sets

deleted_user — Tue, 25 Mar 2008 16:05:17 GMT

Dear SAS experts,

I faced a processing time problem concerning removal of small subset of rows from a data set which contains ~ 200 bill. of rows.

The data set contains indexes for the key variables which are used in WHERE expression to qualify the subset of data to be deleted from the data set.

To rapidly delete the subset, I use a data step:

data DSName;
modify DSName(where=(expression...));
remove;
run;

I think it still takes too much time. Could you please give an idea of faster removal of small subset of data from data set.

Thanks a lot.
Sarunas.

Re: Optimization of processing huge data sets

deleted_user — Tue, 25 Mar 2008 17:04:31 GMT

any feedback from option msglevel= i ; ?
It would clarify whether that where clause is implemented using the index.

have you tried proc sql delete statement ?

Re: Optimization of processing huge data sets

deleted_user — Tue, 25 Mar 2008 19:37:02 GMT

For a table with that many rows, have you considered using the SPD Engine?
I would have put the data in a partitioned Oracle database.

Would using "Key=.." possibly be faster than "(where=...)"

What is the box? What are you using for storage? How are you connected to the storage?

Re: Optimization of processing huge data sets

deleted_user — Wed, 26 Mar 2008 09:12:02 GMT

Thank you Peter and Chuck for hints 🙂

Well, it strange, but proc sql with delete statement, using indexes works twice as fast as data step... 😕

I thought that data step would have to work much faster than procedures.

Well, anyway, proc sql satisfies my deletion time expectations.

Thanks and good luck.
Sarunas

Re: Optimization of processing huge data sets

deleted_user — Wed, 26 Mar 2008 12:47:36 GMT

> Well, it strange, but proc sql with delete statement,
> using indexes works twice as fast as data step... 😕
>
>
> I thought that data step would have to work much
> faster than procedures.

Proc Sql uses a completely different code path than a data step. Data step is much older and tends to be more serialized than SQL, so it tends not to be take advantage of multiple threads/processors as well as SQL can/does.