[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sas-l
Subject:    SAS-L: I did not know you could do that with sql statements
From:       Roger DeAngelis <rogerjdeangelis () GMAIL ! COM>
Date:       2017-06-24 21:48:36
Message-ID: 3755621994251297.WA.rogerjdeangelisgmail.com () listserv ! uga ! edu
[Download RAW message or body]

SAS-L: I did not know you could do that with sql statements 

Feel free to ad any other 'rare' sql statements

   WORKING CODE
   ============

   1.  Select Resolve('%Let '!!Sex!!'='!!Sex!!'#(N='!!Put(Count(*),2.)!!');') as Nul \
From SexGov Group By Sex;

       * good for headings in 'proc report with spliy on '#";

       MALE=   Male  #(N=29)
       FEMALE= Female#(N=21)

   2.  select  *  Tom Abernathy;
           resolve('%let evn=%eval(&evn + 1);') length=1
          ,symget('evn') as seq  length=2

              seq  NAME
           ----------------
              1    Alfred
              2    Alice
              3    Barbara

   3.  Order By (Select Max(Gov) as Gov From SexGov) ;

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

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

    6.  Case
          When Pay > (Select Mean(Pay) from SexGov) Then 1
          Else Pay
        End as Pay

    7.  Group By Sex, Gov
        Having Max(Pay) > ( Select Max(Pay) From SexGov Where Gov='CIA' );

    8.   order by
          case
            when total ne 0 then Total+(-.1*Total+.01*placebo)
            else total
          end

   * 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;


   *  Tom Abernathy;
   %let evn=0;
   proc sql;
     select
         resolve('%let evn=%eval(&evn + 1);') length=1
        ,symget('evn') as seq  length=2
        ,name
     from
        sashelp.class
   ;quit;



   /*--------------------------*\
   | Select in the select clause|
   | N(Pct) Table  Big N Header |
   \*--------------------------*/
   Proc Sql;
     Select Resolve('%Let '!!Sex!!'='!!Sex!!'#(N='!!Put(Count(*),2.)!!');') as Nul \
From SexGov Group By Sex;  quit;
   %put &=male;
   %put &=female;
     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;



   *=================================================;
   * 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;

   *=================================================;
   * 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;
   *=================================================;
   * 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;
   *=================================================;
   * Select Clause on Order Clause                   ;
   * This changes the order of the output            ;
   * 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;

   *Males first then females sorted by age;
     proc sql;
        select
            *
        from
           sashelp.class
        order
           by case (sex)
                when ('M') then -1
                else age
              end
     ;quit;


[prev in list] [next in list] [prev in thread] [next in thread]