Help using Base SAS procedures

Proc Sql - interpret a query

Reply
New Contributor
Posts: 2

Proc Sql - interpret a query

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;

Regular Contributor
Posts: 241

Re: Proc Sql - interpret a query

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 )
*/

Ask a Question
Discussion stats
  • 1 reply
  • 215 views
  • 0 likes
  • 2 in conversation