Optimization of processing huge data sets

[deleted_user]

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.

[deleted_user]

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 ?

[deleted_user]

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

[deleted_user]

> 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.

[deleted_user]

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?