SAS: Track progress inside of a Proc Sql statement

Question

I found this piece of code online

data _null_;
 set sashelp.class;
 if mod(_n_,5)=0 then
  rc = dosubl(cats('SYSECHO "OBS N=',_n_,'";'));
 s = sleep(1); /* contrived delay - 1 second on Windows */
run;

I would like to know if you had any idea of how to adapt this piece to a proc sql statement, so I could track the progress of a long query... For example

proc sql;
create table test as
select * from work.mytable
where mycolumn="thisvalue";
quit;

and somewhere in the statement above we would include the

  rc = dosubl(cats('SYSECHO "OBS N=',_n_,'";'));

Answer 1

You wouldn't be able to directly check the progress of a SQL query, unfortunately (if it's operating on SAS datasets, anyway), except by monitoring the physical size of the table (you can do a directory listing of your WORK directory, or depending on how it's building the table, the Utility directory). However, it may or may not be linear; SQL might, for example, use a hash strategy which would not necessarily take up disk space until it was fairly close to being done.

For SQL, you're best off looking at the query plan to tell how long something's going to take. There are several guides out there, such as The SQL Optimizer Project, which explains the _METHOD and _TREE options among other things.