Proc Sql - interpret a query

TimA — Thu, 19 Jul 2012 13:21:58 GMT

Dear All,

Could you please explain the order and particulars in which SAS 'actions', based on the below query? - perhaps with examples. I am interested when the groupping is done, if it's done several times, when the 'distinct' takes place and what this keyword actually does - in this context, what kind of rows it removes, if it's before or after the groupping is done...

Thank you very much in advance!

PROC SQL;

CREATE TABLE WORK.TABLE1 AS

SELECT DISTINCT t1.'Column1'n, (MAX(t1.Column2'n)) AS 'MAX Value'n,

(CASE WHEN t1.'Column2'n = MAX(t1.'Column2'n) THEN t1.'Column3'n ELSE "" END) AS PL

FROM WORK.TEST AS t1

GROUP BY t1.'Column1'n;

QUIT;

Re: Proc Sql - interpret a query

chang_y_chung_hotmail_com — Thu, 19 Jul 2012 16:08:44 GMT

There is an undocumented option _method, which may give you some idea. Here is an example:

proc sql _method;
select distinct name, max(age) as maxAge
from   sashelp.class
group by sex;
quit;
/* on log
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: SQL execution methods chosen are:
      sqxslct
          sqxunqs
              sqxsumg
                  sqxsort
                      sqxsrc( SASHELP.CLASS )
*/

topic Re: Proc Sql - interpret a query in SAS Procedures

Proc Sql - interpret a query

Re: Proc Sql - interpret a query