SAS: proc sql - select

Format columns

data tq84_data;
  do i = 1 to 20;
     dt = floor(rand('norm') * 5000 + 10000);
     output;
  end;
run;

proc sql;
  select
    i,
    dt format date9.
  from
    tq84_data;
quit;

proc sql;
  select
    min(dt) as min_dt format date9.
  from
    tq84_data;
quit;

Keyword calculated

data tq84_data;
  length num_1 4.
         num_2 4.
         txt   $3;

  input num_1 num_2 txt;

datalines;
13 2 abc
7 913 def
25 40 ghi
873 51 jkl
79 13 mno
645 316 pqr
100 100 stu
run;

proc sql;
  select
    num_1 / num_2        as ratio,
    1 / calculated ratio as ratio_inv,
    txt
  from
    tq84_data
  where
    calculated ratio > 1;
quit;

Where contains

data tq84_data;
  length num   4.
         txt $20;

  input num txt;

datalines;
1 one
2 two
3 three
4 four
5 five
6 six
7 seven
8 eight
9 nine
10 ten
11 eleven
12 twelve
13 thirteen
run;

proc sql;
  select
    *
  from
    tq84_data
  where
    txt contains 've';
quit;

proc sql;
  select
    *
  from
    tq84_data
  where
    txt ? 'ee';
quit;

flow

proc sql;

  create table work.tab (
    foo num,
    bar char(100),
    baz char(100)
  );

  insert into tab
    set foo = 1                    ,
        bar = '123456789 123456879',
        baz = '123456789 123456879';

  insert into tab
    set foo = 2                    ,
        bar = repeat('abcdef'  ,10),
        baz = repeat('ghi'     ,15);

  insert into tab
    set foo = 3                     ,
        bar = repeat('Mnopqr'  , 18),
        baz = repeat('Stuvw '  ,  6);

quit;

proc sql flow = 20;
  select 
    foo,
    bar,
    baz
  from
    work.tab;
quit;

/*
     foo  bar                   baz
----------------------------------------------------
       1  123456789 123456879   123456789 123456879 
       2  abcdefabcdefabcdefab  ghighighighighighigh
          cdefabcdefabcdefabcd  ighighighighighighig
          efabcdefabcdefabcdef  highighi            
          abcdef                                    
       3  MnopqrMnopqrMnopqr    Stuvw Stuvw Stuvw   
          MnopqrMnopqrMnopqr    Stuvw Stuvw         
          MnopqrMnopqrMnopqr    Stuvw Stuvw         
          MnopqrMnopqrMnopqr                        
          MnopqrMnopqrMnopqr                        
          MnopqrMnop         
*/

See also