07-19-2012 09:21 AM
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!
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;
07-19-2012 12:08 PM
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
group by sex;
/* on log
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: SQL execution methods chosen are:
sqxsrc( SASHELP.CLASS )