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;
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 )
*/
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.