%let pgm=utl_SelSql;
/* Using the Select Statement with all other SQL Statments
   There are more direct solutions, but these all use multiple
   select statements */

*=================================================;
*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX;
* Select Clause on Select Statement               ;
*=================================================;

* Create The Data do not change seeds ;

Data  SexGov;
  Do Sex='Female','Male';
    Do Gov='FBI','CIA','IRS';
      Do Ids=10 to 25;
        Serial=Int(1E6*Uniform(5739));
        Pay=Int(50000*Uniform(57343));
        If Uniform(5643) < .5 Then Output;
      End;
    End;
  End;
Run;

/*---------------------------------------------*\
| Output                                        |
|   Sex      Gov    Ids       Pay               |
|                                               |
|  Female    FBI     12    22831.13             |
|  Female    FBI     14    16942.24             |
|  Female    FBI     15     9239.63             |
|  Female    FBI     20    27425.14             |
|  Female    FBI     21     8424.24             |
|  Female    FBI     22     9305.96             |
|  Female    FBI     23    25826.22             |
|                                               |
\*---------------------------------------------*/


/* Put counts in Rectangular array */

Ods Exclude All;
Ods Output Observed=GovSexTbl(Rename=Label=Gov);
Proc Corresp Data=SexGov Observed dim=1;
   Table Gov, Sex;
Run;
Ods Select All;

/*---------------------------------------------*\
| Output                                        |
| Gov      Female        Male         Sum       |
|                                               |
| CIA           6          12          18       |
| FBI           9           6          15       |
| IRS           5           9          14       |
| Sum          20          27          47       |
|                                               |
\*---------------------------------------------*/


Proc Print Data=GovSexTbl;
run;

/*--------------------------*\
| Select in the select clause|
| N(Pct) Table               |
\*--------------------------*/

Proc Sql;
  Select Resolve('%Let '!!Sex!!'='!!Sex!!'#(N='!!Put(Count(*),2.)!!');') as Nul From SexGov Group By Sex;
  Select Max(Sum) into :GrnTot From GovSexTbl;
  Create
    Table GovSex as
  Select
       Gov,
       (Select Max(Sum) From GovSexTbl) as  GrnTot,
       Put(sum(Male),3.)!!'('!!Put(100*Male/&GrnTot.,3.)!!'%)'     as MalCol Label="&Male",
       Put(sum(Female),3.)!!'('!!Put(100*Female/&GrnTot.,3.)!!'%)' as FemCol Label="&Female"
  From
    GovSexTbl
  Group
    By Gov
;
quit;
run;

Proc print Data=GovSex Label Split='#' noobs;
Title1 "Total Number of Government Employees by Sex and Department";
Title2 "&GrnTot. Employees Sampled";
Run;

/*---------------------------------------------*\
| Output                                        |
| Total Number of Government Employees          |
|       47 Employees Sampled                    |
|                                               |
|           Male        Female                  |
| Gov       (N=27)      (N=20)                  |
|                                               |
| CIA      12( 26%)     6( 13%)                 |
| FBI       6( 13%)     9( 19%)                 |
| IRS       9( 19%)     5( 11%)                 |
| Sum      27( 57%)    20( 43%)                 |
|                                               |
\*---------------------------------------------*/


*=================================================;
*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX;
* Select Clause on Drom Statement                 ;
*=================================================;


/*--------------------------*\
| All possible combinations  |
\*--------------------------*/

Data
     Cat;

  Do Cat='Cop','Cab','Jan';

     Output;
  End;

Run;

/*---------------------------------------------*\
| SexGov table                                  |
|   Sex      Gov    Ids       Pay               |
|                                               |
|  Female    FBI     12    22831.13             |
|  Female    FBI     14    16942.24             |
|  Female    FBI     15     9239.63             |
|  Female    FBI     20    27425.14             |
|  Female    FBI     21     8424.24             |
|  Female    FBI     22     9305.96             |
|  Female    FBI     23    25826.22             |
|                                               |
| Cat Table                                     |
|  Cop                                          |
|  Cab                                          |
|  Jan                                          |
|                                               |
\*---------------------------------------------*/


Proc print;
run;

Proc Sql;

  Title "All Combinatons of Sex Gov with Cat";

  Select
         Distinct *
  From
        (Select Sex, Gov from SexGov), Cat
;
quit;
run;

/*---------------------------*\
| Output all 18 combinations  |
|                             |
|   Sex     Gov  Cat          |
|   ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ          |
|   Female  CIA  Cab          |
|   Female  CIA  Cop          |
|   Female  CIA  Jan          |
|   Female  FBI  Cab          |
|   Female  FBI  Cop          |
|   Female  FBI  Jan          |
|   Female  IRS  Cab          |
|   Female  IRS  Cop          |
|   Female  IRS  Jan          |
|   Male    CIA  Cab          |
|   Male    CIA  Cop          |
|   Male    CIA  Jan          |
|   Male    FBI  Cab          |
|   Male    FBI  Cop          |
|   Male    FBI  Jan          |
|   Male    IRS  Cab          |
|   Male    IRS  Cop          |
|   Male    IRS  Jan          |
|                             |
\*---------------------------*/



*=================================================;
*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX;
* Select Clause on Where with any and all logic   ;
*=================================================;


Proc sql;

 /* Pay greater than all Female employees */
 select Serial, Sex, Gov, Pay
 from SexGov
 where Pay  >
 all (select Pay from SexGov where Sex= 'Female');

 /* Pay greater than any Female employees */
 select Serial, Sex, Gov, Pay
 from SexGov
 where Pay  >
 any (select Pay from SexGov where Sex= 'Female');

 /* Pay greater than all Female employees */
 Select Serial, Sex, Gov, Pay
 from SexGov
 Where
     (Select Max(Pay) from SexGov Where Sex='Male' ) > all
     (Select Pay from SexGov Where Sex='Female');

quit;
run;


*=================================================;
*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX;
* Select Clause on Case Statement                 ;
*=================================================;


Proc Sql;

 /* Set Oay to 1 if > then overall mean pay */

 select Serial, Sex, Gov,
    Case
      When Pay > (Select Mean(Pay) from SexGov) Then 1
      Else Pay
    End as Pay
 from SexGov;

quit;

/*---------------------------------------------*\
|                                               |
| Output                                        |
|                                               |
|                                               |
|  Serial  Sex     Gov       Pay                |
|  ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ                |
|   25439  Female  FBI      3463                |
|  629949  Female  FBI         1                |
|  851828  Female  FBI         1                |
|   66992  Female  FBI     19882                |
|  659046  Female  FBI         1                |
|  248322  Female  FBI     17126                |
|  102059  Female  CIA      4111                |
|  951256  Female  CIA         1                |
|  440843  Female  CIA         1                |
|  924779  Female  CIA         1                |
|  374071  Female  CIA      7690                |
|  256964  Female  IRS         1                |
|  587027  Female  IRS         1                |
|  581008  Female  IRS         1                |
|  377557  Female  IRS      4634                |
|  639906  Female  IRS         1                |
|                                               |
|                                               |
\*---------------------------------------------*/


*=================================================;
*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX;
* Select Clause on Having Clause                  ;
*=================================================;

 Proc sql;

 select Serial, Sex, Gov
 from SexGov
 Group By Sex, Gov
 Having Max(Pay) > ( Select Max(Pay) From SexGov Where Gov='CIA' );

 quit;
Run;


*=================================================;
*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX;
* Select Clause on Order Clause                   ;
* This changes the order of the output            ;
* No Errors and does change the order             ;
* But I have no idea what it is doing             ;
*=================================================;

Proc sql;
 select Gov, Sex, Pay
 from SexGov
 Order By (Select Max(Gov) as Gov From SexGov) ;
 quit;
Run;