proc sql;
create TABLE thesis.Slideback_F AS
select t1.'HUB Cust Id'n ,t1.'Version Date'n
from thesis.Slideback_v1 as t1 inner join cnmap.VIEW_MAPRH_M_CN_CUSTOMER_PFS as t2
on t1.'HUB Cust Id'n =t2.'HUB Cust Id'n and t1.'Version Date'n =t2.'Ver Date'n
where (t1.'Version Date'n-t2.'Date Requested Added'n)/3600/24>=120;
Quit;
Note:'HUB Cust Id'n is customer id,'Version Date'n is like '30SEP2011:00:00:00'
'HUB Cust Id'n +'Version Date'n is the primary key in
cnmap.VIEW_MAPRH_M_CN_CUSTOMER (oracle database
)
observation in thesis.Slideback_F is 7300.
observation in cnmap.VIEW_MAPRH_M_CN_CUSTOMER_PFS is about 24 months * 100k(per month)
sas log
real time 10:19.60
CPU time 9.48 s
how to short real time based on the above code?
need more information.
Is thesis also an oracle database on the same server?
If not, then SAS is transferring all the data from the Oracle database to your SAS server for execution. That would involve a lot of network and disk I/O and explain the discrepancy between the real time and the CPU time.
Two ways to shorten the real time:
-- move the thesis data into tables on the Oracle database so the join executes there.
-- if that is not feasible, subset your Oracle data before transferring it to SAS.
Doc Muhlbaier
Duke
Duke,
Thanks for your reply.
1.Thesis is a library opened in sas server, that is to say thesis.Slideback_F is stored in sas server.
2.The oracle database is readable only, can not put thesis.Slideback_F there.
3.Can you explain in detail that how to "subset your Oracle data before transferring it to SAS"?
Thanks.
Dawn
How about put them all into a condition ? and try to balance equation .
on t1.'HUB Cust Id'n =t2.'HUB Cust Id'n and t1.'Version Date'n =t2.'Ver Date'n
and t1.'Version Date'n>=(120*3600/24) + t2.'Date Requested Added'n;
Ksharp,
real time 10:21.99
CPU time 10.23 秒
almost equal to the former SQL.
Anyway, thanks for giving another thought.
The real time is the clock time from the start of Proc SQL.
CPU time is the SAS part of the execution. So, one can assume that most CPU cycles is used to extract data from Oracle.
Once in SAS, and the join, it will seems pretty fast.
I don't know about Oracle index strategies, but it's usually a problem to have a calculation in the where, since then the optimizer will use full tablescan strategy.
By adding
options sastrace=',,,d' sastraceloc=saslog;
Oracle will echo the Oracle part of the query. I suggest you use that to discuss to an Oracle DBA/expert about optimize that part of the query.
/Linus
Linus,Thanks for your suggestion.I'll try.
Another option if the Thesis.Slideback_V1 is substantially smaller than the Oracle table, try the DBKEY= option:
If you can at least get the create temporary table authorization in Oracle, you can look at the Oracle temporary tables support for SAS/ACCESS (also in the online doc). Use that to upload your thesis table to Oracle.
Haha. That doesn't work.
Maybe you can try some options to speed it. Still I don't know whether it is worked.
System Option Description
DBIDIRECTEXEC= Controls SQL optimization for SAS/ACCESS engines.
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.