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
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
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
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
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;
sorry it doesn't work, it has running for minutes,i have to stop it.
Have you encountered any error.it should have worked as it is same thing including where in set statment.
Is macro getting resolved?
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
&CurDate used in the program is macro variable.
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
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
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
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
ohk.So yuo are acessing oracle database. Try using sql passthrough and your query would be run faster.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.