topic Re: Efficiency problem on "data set" and proc sql? in SAS Procedures

Efficiency problem on "data set" and proc sql?

bbb_NG — Mon, 09 Apr 2012 08:47:06 GMT

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

Re: Efficiency problem on "data set" and proc sql?

Ksharp — Mon, 09 Apr 2012 08:53:13 GMT

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

Re: Efficiency problem on "data set" and proc sql?

bbb_NG — Mon, 09 Apr 2012 09:04:21 GMT

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

Re: Efficiency problem on "data set" and proc sql?

manojinpec — Mon, 09 Apr 2012 09:10:35 GMT

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;

Re: Efficiency problem on "data set" and proc sql?

bbb_NG — Mon, 09 Apr 2012 09:21:05 GMT

manojinpec ,

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

Re: Efficiency problem on "data set" and proc sql?

Ksharp — Mon, 09 Apr 2012 10:42:57 GMT

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

Re: Efficiency problem on "data set" and proc sql?

manojinpec — Mon, 09 Apr 2012 11:36:49 GMT

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

Is macro getting resolved?

Re: Efficiency problem on "data set" and proc sql?

bbb_NG — Tue, 10 Apr 2012 01:34:41 GMT

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

Re: Efficiency problem on "data set" and proc sql?

manojinpec — Tue, 10 Apr 2012 04:01:11 GMT

&CurDate used in the program is macro variable.

Re: Efficiency problem on "data set" and proc sql?

bbb_NG — Tue, 10 Apr 2012 06:07:38 GMT

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

Re: Efficiency problem on "data set" and proc sql?

shivas — Tue, 10 Apr 2012 06:55:06 GMT

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

Re: Efficiency problem on "data set" and proc sql?

bbb_NG — Tue, 10 Apr 2012 07:03:13 GMT

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

Re: Efficiency problem on "data set" and proc sql?

Ksharp — Tue, 10 Apr 2012 07:08:12 GMT

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

Re: Efficiency problem on "data set" and proc sql?

manojinpec — Tue, 10 Apr 2012 07:10:03 GMT

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