Hi,
I have this code from a SAS-supplied solution macro:
proc sql;
update seg_kc.fsc_entity_watch_list_dim
set change_end_date=dhms(input(put(&runasofdate,8.),yymmdd8.),00,00,00),
change_current_ind = 'N'
where upcase(watch_list_name) = 'WCHK' and
upcase(change_current_ind) = 'Y' and
entity_watch_list_number in
(select entity_watch_list_number
from STG_WTCH.wl_wchk_delete_load);
quit;
I've modified it to close out all records, not just those matching the sub-query:
proc sql;
update seg_kc.fsc_entity_watch_list_dim
set change_end_date=dhms(input(put(&runasofdate,8.),yymmdd8.),00,00,00),
change_current_ind = 'N'
where upcase(watch_list_name) = 'WCHK' and
upcase(change_current_ind) = 'Y';
quit;
However, it is taking forever to run.
I've always been taught not to call a function for every record in the dataset/table if that function is in fact just returning a constant. I was also hoping the below would make PROC SQL use implicit pass-through to Oracle:
%* get runasofdate ;
%fcf_get_runasofdate;
%put;
%put >>> RUNASOFDATE: &runasofdate;
%put;
%* convert to datetime - this will execute once, at compile time ;
%let datetime=%sysfunc(putn(%sysfunc(inputn(&runasofdate,yymmdd8.)),date9.)):00:00:00;
%put >>> DATETIME: &datetime;
proc sql noprint;
update seg_kc.fsc_entity_watch_list_dim
set
change_end_date = "&datetime"dt,
change_current_ind = 'N'
where
upcase(watch_list_name) in ('WCHK','WORLDCHECK')
and
upcase(change_current_ind) = 'Y'
;
quit;
But this is still taking forever.
I can convert this to explicit pass-through, which performs well, but there may be issues with that when crossing environments (DEV/TEST/PROD) - I'd have to investigate that further.
So, is there a way convert the above code to implicit pass-through, or otherwise improve the query performance?
Thanks...
Scott, When implicit is wanted then:
1/ your first check should be on the used functions the must be available in the RDBMS
2/ You could follow (obs=0) the SQL translation often the SQL is tried behind curtains and when failing all data is copied to the sas-machine. The sqltrace option is a must for analyzing what is happening. A nice paper. http://support.sas.com/resources/papers/proceedings13/072-2013.pdf
3/ dates / datatimes quotings sqlconstdatetime and sqlreduceput are eliminating those sas specifics before trying to convert it to Oracle Sql http://support.sas.com/documentation/cdl/en/sqlproc/68053/HTML/default/viewer.htm#p12ohgh32ffm6un13s...
The quotation in SQL is normally single quotes in ANSI SQL for constant values. SAS is using the double-quotes when allowing macro vars it can be nedessary to stick to those single ones. Thena use %nrstr(%') for that single quote.
The Date-constant should be translated to ansi standards. http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm#BABGIGCJ
The usage of date/time conventions as of SAS habits can cause problems. I have seen several posts indicating the automatic translation (SAS/access) is failing.
4/ forcing dbmust
Hi Scott,
I would expect that your last SQL using change_end_date = "&datetime"dt will convert nicely into Oracle SQL and will execute fully in-database. As @jakarman points out option "SASTRACE" will tell you what's going on behind the scene and how much of the SAS SQL gets converted and sent to Oracle for execution.
Defensive programming is generally a good thing BUT column "watch_list_name" is indexed; by applying an upcase() function on this column the index can't get used.
where upcase(watch_list_name) = 'WCHK' and
upcase(change_current_ind) = 'Y';
I believe for "watch_list_name" and "change_current_ind" the load process needs to ensure that the values get loaded all uppercase so that any subsequent query doesn't need to use upcase() functions.
Thanks,
Patrick
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.