/*****************************************************************************************************************

SAS file name: Select_ByGroup.sas
File location: 
_________________________________________________________________________________________________________________

Purpose: To demonstrate how to select an entire By Gruoup in a data set based on a condition regarding a single observation using PROC SQL
Author: Peter Clemmensen
Creation Date: 14/12/2017

This program supports the post "Select By Group Conditional on Observation" on SASnrd.com

*****************************************************************************************************************/

/* Example 1 - Subquery */
proc sql;
    create table stocks_subquery as
        select *
        from sashelp.stocks
        where stock in (select distinct stock
                                from sashelp.stocks
                                where volume>10e7)
        order by stock, date;
quit;

/* Example 2 - Having clause */
proc sql;
    create table stocks_havingclause as
        select *
        from sashelp.stocks
        group by stock
        having sum(Volume > 10e7) > 0
        order by stock, date;
quit;

/* Compare the two data sets */
proc compare data=stocks_subquery compare=stocks_havingclause;
run;