Efficiency problem on "data set" and proc sql?

[bbb_NG]

Solution 1:

DATA wuTemp.CustTRBcur (keep='Version Date'n         'HUB Cust Id'n           'Last Month Average Balance'n     ) ;

SET CNMAP.VIEW_MAPRH_M_CN_CUS_TRB ; if 'Version Date'n=&CurDate and 'Last Month Average Balance'n>=0;

run;

Solution 2:

proc sql;

create table wuTemp.CustTRBcur as

select 'Version Date'n   ,  'HUB Cust Id'n   ,     'Last Month Average Balance'n 

from CNMAP.VIEW_MAPRH_M_CN_CUS_TRB

where 'Version Date'n=&CurDate and 'Last Month Average Balance'n>=0;

quit;

I wonder why solution 2 is much quicker than solution 1 on the condition that in database CNMAP, it will keep 24 latest months records.

can anyone tells why?

thanks

Dawn

[Ksharp]

Because you have used WHERE at SQL.

If you also used WHERE instead of IF in data step, I believe they would have the speed.

Ksharp

[bbb_NG]

Ksharp,

Thanks for your comments.

I've tried,now it's tolerable.

when replacing if with where in solution1

the extraction time drops dramatically

to 38.15 s

and solution 2 is only 24.29 s

[Ksharp]

En...........

Then try this code.

DATA wuTemp.CustTRBcur ;

SET CNMAP.VIEW_MAPRH_M_CN_CUS_TRB

(keep='Version Date'n    'HUB Cust Id'n     'Last Month Average Balance'n   

  where=('Version Date'n=&CurDate and 'Last Month Average Balance'n>=0) );

run;

Ksharp

[manojinpec]

Try below and see if there is any improvement.

Solution 1:

DATA wuTemp.CustTRBcur (keep='Version Date'n         'HUB Cust Id'n           'Last Month Average Balance'n     where=('Version Date'n=&CurDate and 'Last Month Average Balance'n>=0)) ;

SET CNMAP.VIEW_MAPRH_M_CN_CUS_TRB ;

run;

Solution 2:

proc sql;

create table wuTemp.CustTRBcur as

select 'Version Date'n   ,  'HUB Cust Id'n   ,     'Last Month Average Balance'n 

from CNMAP.VIEW_MAPRH_M_CN_CUS_TRB

where 'Version Date'n=&CurDate and 'Last Month Average Balance'n>=0;

quit;

[bbb_NG]

manojinpec ,

sorry it doesn't work, it has running for minutes,i have to stop it.

[manojinpec]

Have you encountered any error.it should have worked as it is same thing including where in set statment.

Is macro getting resolved?

[bbb_NG]

manojinpec ,

No error occurred, just lasting than several minutes, stimulating my patience.

One thing to clear ,that no macro is involved.

Thank you anyway for giving another thinking.

dawn

[manojinpec]

&CurDate used in the program is macro variable.

[bbb_NG]

manojinpec ,

DATA wuTemp.CustTRBcur (keep='Version Date'n         'HUB Cust Id'n           'Last Month Average Balance'n     where=('Version Date'n='31mar2012:0:0:0'dt and 'Last Month Average Balance'n>=0)) ;

SET CNMAP.VIEW_MAPRH_M_CN_CUS_TRB ;

run;

Thanks for your kind remiding, I revised &curdate with specific value, but it sitll counting minutes. Have to stop it.

while replace &curdate with specific value in solution 2, it really saves time for less than one seconds.

Dawn

[shivas]

Hi,

Just run the code in reverse order...first run the SQL code and then try to run the data step code then you can find the difference.

Data step code will show good performance.

Solution 2:

proc sql;

create table wuTemp.CustTRBcur as

select 'Version Date'n   ,  'HUB Cust Id'n   ,     'Last Month Average Balance'n

from CNMAP.VIEW_MAPRH_M_CN_CUS_TRB

where 'Version Date'n=&CurDate and 'Last Month Average Balance'n>=0;

quit;

Solution 1:

DATA wuTemp.CustTRBcur (keep='Version Date'n         'HUB Cust Id'n           'Last Month Average Balance'n     ) ;

SET CNMAP.VIEW_MAPRH_M_CN_CUS_TRB ; where 'Version Date'n=&CurDate and 'Last Month Average Balance'n>=0;

run;

Thanks,

Shiva

[bbb_NG]

Shiva,

Thanks for your comments.

I know that data should be more efficient than proc sql, I run solution1 & 2 not one by one in queue, but with one have output then choose to run another.

So I am confused. Maybe it can attribute to the oracle database I am accessing.

thanks.

Dawn

[Ksharp]

Then try this code. Whatever it is SQL or DataStep, they will both use Index of Oracle if you define  a WHERE statement.

DATA wuTemp.CustTRBcur ;

SET CNMAP.VIEW_MAPRH_M_CN_CUS_TRB

(keep='Version Date'n    'HUB Cust Id'n     'Last Month Average Balance'n   

  where=('Version Date'n=&CurDate and 'Last Month Average Balance'n>=0) );

run;

Ksharp

[manojinpec]

ohk.So yuo are acessing oracle database. Try using sql passthrough and your query would be run faster.