Help using Base SAS procedures

Efficiency problem on "data set" and proc sql?

Reply
Frequent Contributor
Posts: 99

Efficiency problem on "data set" and proc sql?

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

Super User
Posts: 10,018

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

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

Frequent Contributor
Posts: 99

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


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

Super User
Posts: 10,018

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

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

Frequent Contributor
Posts: 139

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

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;

Frequent Contributor
Posts: 99

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

Posted in reply to manojinpec

manojinpec ,

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

Frequent Contributor
Posts: 139

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

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

Is macro getting resolved?

Frequent Contributor
Posts: 99

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

Posted in reply to manojinpec

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

Frequent Contributor
Posts: 139

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

&CurDate used in the program is macro variable.

Frequent Contributor
Posts: 99

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

Posted in reply to manojinpec

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

Super Contributor
Posts: 349

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

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

Frequent Contributor
Posts: 99

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

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

Super User
Posts: 10,018

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

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

Frequent Contributor
Posts: 139

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

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

Ask a Question
Discussion stats
  • 13 replies
  • 367 views
  • 3 likes
  • 4 in conversation