| Base SAS | proc sql | Data Processing |
|---|---|---|
| Dataset | Table | File |
| Observation | Row | Record |
| Variable | Column | Field |
| Merge | Join | Join |
| Subsetting | Query |
*1. Create table according to specifications:
create table sql.pop(
continent char(15), population num
);
*2. Create table with same column attributes
as an existing table:
create table new_table like sql.pop;
*3. Copy entire table;
create table new_table as
select * from sql.pop;
*4. Delete rows from table:
delete from sql.pop where population < 1000000;
*5. Insert rows into table;
* Method 1
insert into sql.pop
values('Africa', 706611183)
values('Asia', 3379469458);
* Method 2
insert into sql.pop
set continent='Africa', population=706611183
set continent='Asia', popupation=3379469458;
* Method 3
insert into new_table
select * from sql.pop;
*6. Write the create table statement showing
the column attributes for an existing
table to the SAS log;
describe table sql.pop;
*7. Add column to table:
alter table sql.pop add largest_city char(15);
*8. Modify table column:
alter table sql.pop modify population format 15.;
*9. Update table fields:
update sql.pop set density = population / area;
*10. Delete table column:
alter table sql.pop drop continent;
*11. Drop entire table:
drop table sql.pop;
select * from table_name;
select from where group by having order byOnly the select and from clauses are required.
| Key Word | Meaning |
|---|---|
| select | Specify Columns for Query |
| from | Source Table(s) for Query |
| where | Specify Rows for Query |
| group by | Specify Groupings for Summary Statistics |
| having | Filter Grouped Data |
| order by | Sort Table Rows |
| having Clause | where Clause |
|---|---|
| specify conditions for including or excluding groups. |
specify conditions for including or excluding individual observations. |
| must follow group by clause if group by is used. |
must precede group by if group by is used. |
| affected by group by clause; if there is no group by, having is treated as group by. | is not affected by group by clause. |
| processed after group by and any aggregate function. | processed before group by clause, if there is one, and before any aggregate functions. |
count,freq,n css cv max mean,avg min nmiss prt range std stderr sum sumwgt t uss var