topic Re: How to create index before loading the data into the Output table in SAS Programming

How to create index before loading the data into the Output table

VVDR — Wed, 23 Sep 2020 01:39:43 GMT

Hi,

I have a table with huge volume of data (approx 500 million records) and I am using SQL Join transformation ( and doing aggregate functions, group by and having in clause). I have also created Index(on the columns that are used in the having clause) on the output table( by right clicking on the temporary output table).

But it is taking ages to exeute. Can someone help me here.

Thanks

Re: How to create index before loading the data into the Output table

jimbarbour — Wed, 23 Sep 2020 02:43:58 GMT

First, how many columns are we talking about here? If you've got columns that are not necessary to calculations or the final results, eliminate them from the SELECT.

Second, can you post the code? It's pretty hard to guess at what you're doing here.

Jim

Re: How to create index before loading the data into the Output table

VVDR — Wed, 23 Sep 2020 04:42:06 GMT

Hi @jimbarbour

I have only 13 columns after deleting the unwanted columns.

Sample Code:

Block1:

Proc sql;

Create table T1 as select * from Original

where date>2010 and itemcategory not in ('A','B','C');

quit;

Block2:

Proc sql;

Create table T2 as select name, area, designation, sum(salary) as Totalsum

from T1;

Quit;

Block3:

Proc Sql;

Create table T3 as select * from T2

where Totalsum>1000;

Quit;

The above one is the sample code.

Please suggest.

Thanks

Re: How to create index before loading the data into the Output table

andreas_lds — Wed, 23 Sep 2020 04:56:49 GMT

Without seeing the code and the log it is hardly possible to suggest something useful.

Re: How to create index before loading the data into the Output table

andreas_lds — Wed, 23 Sep 2020 04:58:35 GMT

@VVDR wrote:

Hi @jimbarbour

I have only 13 columns after deleting the unwanted columns.

Sample Code:

Block1:

Proc sql;

Create table T1 as select * from Original

where date>2010 and itemcategory not in ('A','B','C');

quit;

Block2:

Proc sql;

Create table T2 as select name, area, designation, sum(salary) as Totalsum

from T1;

Quit;

Block3:

Proc Sql;

Create table T3 as select * from T2

where Totalsum>1000;

Quit;

The above one is the sample code.

Please suggest.

Thanks

In the code you have posted only datasets in work are used. Please don't post a simplified version of the code you want to be optimized.

Re: How to create index before loading the data into the Output table

Kurt_Bremser — Wed, 23 Sep 2020 05:52:29 GMT

This code does not make sense. In block 2, you create the sum of salary over the whole dataset and attach it to every single observation of the dataset. So if that sum is larger than 1000, you will still get all observations in block 3; if it's smaller, you get none.

Please describe what you actually want to achieve.