Sql Chapter Two

group by, having, order by);
QUIT;
Note:  place semicolon at the end of the last clause only.

following features:
-selects data that meets certain conditions
-groups data
-specifies an order for the data
-formats data
-calculates new variables

data based on a condition - optional
group by - classifies the data into groups - optional
having - subsets groups of data based on a group condition
order by - sorts row by the values of specific columns
Note:  the order of the clauses are significant.

statement syntax are:
validate - used to check the select statement syntax
noexec - checks for invalid syntax in all types of SQL statements

statement has valid syntax.

proc sql; validate select timemile, restpulse, maxpulse, from project.fitness where timemile gt 7;
 Syntax error, expecting one of the following: a quoted string, !, !!, &...

not executed due to NOEXEC option.

validity of column name
-prints error messages for invalid queries
-is only used for select statements

Features of noexec:
-Checks for invalid syntax in all types of SQL statements

columns
-Calculating columns using a CASE expression

in the order that they were stored, use an asterisk in the SELECT statement:

 
PROC SQL;
SELECT *
FROM VITALS;  
QUIT;

          PATIENT     PULSE      TEMP       BPS       BPD                        
  101        72      98.5       130        88                               101        75      98.6       133        92                               101        74      98.5       136        90                               102        81        99       141        93                               102        77      98.7       144        97                               102        78      98.7       142        93                               103        77      98.3       137        79                               103        77      98.5       133        74                               103        78      98.6       140        80                               103        75      99.2       147        89                               104        72      98.8       128        83                               104        69      99.1       131        86                   

all columns in a table in the order that they were stored, you can specify the columns to be printed in the order that you want them in the SELECT statement or CASE EXPRESSION in the select statement .

PROC SQL; CREATE TABLE TESTMED AS SELECT PATIENT, CASE ((PATIENT/2 = INT(PATIENT/2)) + (PATIENT = .)) WHEN 1 THEN 'Med A'
WHEN 0 THEN 'Med B'
ELSE 'Error' 
END AS DOSEGRP LENGTH=5 FROM VITALS ORDER BY PATIENT; QUIT;

PATIENT    DOSEGRP
   101       Med B
   101       Med B  
   101       Med B    102       Med A
   102       Med A    102       Med A
   103       Med B
   103       Med B
   103       Med B
   103       Med B
   104       Med A
   104       Med A
   104       Med A  

an existing column and then naming the new column using the as function.

Calculate the proportion of Units form each country
    CODE:

OUTPUT:

book that dates are stored in a different format when run through SAS. 
We will then use these dates to calculate new columns.

create a new column

CODE:

OUTPUT:

from an SQL query, use create table.

 
proc sql; create table states as select state_code, state_name
from d2data.state; quit;

 State_ Obs Code State_Name      99   UT       Utah               100             VT    Vermont            101  VA      Virginia            102     WA      Washington 
103     WV      West Virginia       
104     WI     Wisconsin  
105   WY      Wyoming 
106             N/A   

rows
-Subsetting using where clauses, escape clauses, and calculated values

d2data.order_item  
where total_retail_price > 1000;  
quit;

           Large orders                      Total Retail Price
  Product ID For This Product 
240200100076      $1,796.00 
240400200097      $1,250.40
240100400043      $1,064.00 
240200200013      $1,266.00 
240300100032      $1,200.20 240300300070      $1,514.40 
230100700009      $1,687.50   
230100700008      $1,542.60 
240300300090      $1,561.80
 230100700009      $1,136.20  
230100200025      $1,103.60 
240200100173      $1,937.20

fulfill before being selected. CODE:

OUTPUT:
Where clauses uses common comparisons (lt, gt, eq, etc) and logical operators (OR, Not, And, In, Is Null, ...). 

that will indicate how proc sql will interpret LIKE wildcards when SAS is searching within a character string.  

CODE:

OUTPUT:

Example: Select observations from a string variable containing an underscore ('_').

it's possible for an error to show up since the columns used in the where clause must exist in the table or be derived from an existing column. 
There are two fixes for this, the first would be repeating the calculation in the where clause.  The alternative method would be using CALCULATED keyword to refer to an already calculated column in the select.

costprice_per_Unit) as profit       from d2data.order_item
   where calculated profit < 3; quit; title;

  Lack of profit        Product ID    profit 
230100500045       0.7
230100500068       0.9 240100100433      1.85
240700200004         2 240200100021       1.5
240100100031       2.4 240700200007       2.9
240100100232       1.9
230100500004      1.85
230100500004      1.85
240700100017     -1.41