Lesson 1: SQL as a calculator 1

# an example

data null; y=.;

PROC SQL;

  SELECT 2+3, 11*3, 2**5, cos(constant("pi")/3), exp(-1/2), log(2)

  FROM null

  ; QUIT;

# an alternative,  which I prefer

PROC SQL inobs=1;   

  SELECT 2+3, 11*3, 2**5, cos(constant("pi")/3), exp(-1/2), log(2)

  FROM sashelp.cars

  ; QUIT;

Note: columns in SELECT should be separated by ",". 

Note: SQL needs a table after FROM. A dummy dataset "null" is created to feed it.

PROC SQL inobs=1;   

  SELECT 2+3, 11*3, 2**5, cos(constant("pi")/3), exp(-1/2), log(2)

  FROM sashelp.cars

  ; QUIT;

----------------------------------------------------------
5 33 32 0.5 0.606531 0.693147

  

Note: Any SAS system has SASHELP library, in which there are lots of datasets including CARS. We can use it to feed FROM with inobs=1 to read just one line from the dataset. If you skip inobs=1, you will see the consequence. outobs=1 also works with a bugging warning in LOG.

# put a label; this is just a label to print in output

PROC SQL inobs=1;

  SELECT cos(constant("pi")/3) "cos(pi/3)"

  FROM sashelp.cars

  ; QUIT;

cos(pi/3)
---------
0.5

# name a column, which can be used to derived another value

PROC SQL inobs=1;

  SELECT cos(constant("pi")/3) as cospi3, 10*calculated cospi3

  FROM sashelp.cars

  ; QUIT;

cospi3
------------------
0.5 5

Note: a derived value can be name and be used later. When it is used later, it needs "calculated" in front of the column name.

# format: display as you like;

PRCO SQL inobs=1;
  SELECT exp(-1/2) format=8.3, log(2) format=8.4, '25DEC2018'D format=yymmdd10.
  FROM sashelp.cars
  ; QUIT;

------------------------------
0.607 0.6931 2018-12-25