Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

SAS Advanced Cert Exam > Combining, Creating and Managing Tables with PROC SQL > Flashcards

Combining, Creating and Managing Tables with PROC SQL Flashcards

1
Q

In all types of joins, what does PROC SQL generate first?

A

a Cartesian product (i.e. all possible combinations of values between the two tables)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What happens when you specify multiple tables in the FROM clause but don’t include a WHERE statement?

A

PROC SQL returns the Cartesian product of the tables (i.e. all possible combinations of the values in the two tables)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

How to do an inner join?

A

proc sql;

select *

from table 1, table 2

where table1.column = table2.column;

quit;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What happens if you do an inner join with tables in which multiple rows have duplicate values of the column on which the tables are being joined?

e.g.

X A X B

2 b1 2 x1

2 b2 2 x2

A

All possible combinations of the duplicate rows are displayed:

X A X B

2 b1 2 x1

2 b1 2 x2

2 b2 2 x1

2 b2 2x2

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

How do you assign a table alias?

What are two situations that require a table alias?

A

proc sql;

select t1.column1 column2

from table1 as t1, table2 as t2

where t1.column1 = t2.column1;

Table aliases are required when

  1. a table is joined to itself: e.g. from airline.staffmaster as s1, airline.staffmaster as s2
  2. you need to reference columns from same-named tables in different libraries: e.g. from airline.flightdelays as af, work.flightdelays as wf
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What are the types of outer joins?

A
  1. Left: all matching rows plus nonmatching rows from the first table specified in the FROM clause (the left table)
  2. Right: all matching rows plus nonmatching rows from the second table specified in the FROM clause (the right table)
  3. Full: All matching rows plus nonmatching rows in both tables
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Outer join syntax

A

Note you can do an outer join on only two tables or views at a time.

proc sql;

select column1 colum2..

from table1

left join/right join/full join

table2

on join-conditions;

e.g.

proc sql;

select *

from one

left join

two

on one.x=two.x;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Syntax for creating an inner join with outer join syntax

A

proc sql;

select column1 column2..

from table1

inner join

table2

on join-conditions;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What are 3 advantages of PROC SQL joins over DATA step match-merges?

A
  1. PROC SQL joins don’t require sorted or indexed tables
  2. PROC SQL joins do not require that the columns in join expressions have the same name
  3. PROC SQL joins can use comparison operators other than the equal sign, e.g.

proc sql;

selec a.itemnumber, cost, price

from table 1 as a, table 2 as bb

where a.itemnumber = b.itemnumber and a.cost>b.price;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What is the difference between DATA step match-merge and PROC SQL joins when all the values of the merging variable match?

when only some of the values match?

When only some of the values match, how can you make a PROC SQL full outer join similar to a DATA step match-merge?

A
  1. No difference
  2. The DATA step overlays the two common columns by default, PROC SQL does not (so there will be some missing values in the column) see pg. 103
  3. Use COALESCE function, eg.

proc sql;

select coalesce(three.x, four.x) as X, a , b

from three

full join

four

on three.x = four.x;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What is an in-line view?

A

An in-line view exists only during query execution

An in-line view is a nested query that is specified in the outer query’s FROM clause. It selects data from one or more tables in order to produce a temporary table that the outer query then uses to select data for output. e.g.

from (select flighnumber, date

from sasuser.marchflights;)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Two potential advantages of using an in-line view?

A
  1. The complexity of the code is usually reduced, so that the code is easier to write and understand
  2. In some cases, PROC SQL might be able to process the code more efficiently
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Create a FROM clause with a table and an in-line view and assign as aliases to both of them. Don’t forget the where statement.

A

from table1 as one,

(select column1, column2,

from table2) as two

where one.column1 = two.column2

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Syntax to combine tables vertically

A

proc sql;

select *

from table1

except/intersect/union/outer union

select *

from table2;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Which set operator is evaluated first by default?

A

intersect

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Syntax to use ALL and CORR

A

proc sql;

select *

from table1

set-operator all/corr

select *

from table2;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

When do you use the ALL keyword?

A

cannot be used with OUTER UNION

When you want it to make only one pass through the data and not remove duplicate rows

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

When do you use the CORR keyword?

A

When you want it to compare and overlay columns by name instead of position

When used with EXCEPT, INTERSECT, and UNION, removes any columns that do not have the same name in both tables

When used with OUTER UNION, overlays same-named columns and displays columns that have nonmatching names without overlaying.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What set-operator is represented by the picture?

What two things does it do?

A

EXCEPT

Selects unique rows from the first table that are not found in the second table

overlays columns

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

What type of join is represented by the picture?

A

inner join

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

What type of join is represented by the picture?

A

full outer join

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What type of join is represented by the picture?

A

left outer join

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What type of join is represented by the picture?

A

right outer join

24
Q

Table one

X: 1 1 1 2 3 4 6

A: a a b c v e g

Table two

X: 1 2 3 3 5

B: x y z v w

What is the output of

proc sql;

select *

from one

except all corr

select *

from two;

A

X: 1 1 4 6

Because of CORR, PROC SQL only looks at the column that has the same name on both tables. Because of ALL, duplicates are not eliminated. Then the numbers in table one that have a match in table two are eliminated. Note that this is a one-to-one match. Only one of the 1s in table one is eliminated.

25
Q

What set operator is represented by the image?

What two things does it do?

A

INTERSECT

Selects unique rows that are common to both tables

Overlays columns

26
Q

Adding what keyword to a PROC SQL query prevents PROC SQL from making an extra pass through the data?

A

ALL

27
Q

Table One

X: 1 1 1 2 3 4 6

A: a a b c v e g

Table Two

X: 1 2 3 3 5

B: x y z v w

What is the output of:

proc sql;

select *

from one

intersect all corr

select *

from two;

A

X: 1 2 3

Because of CORR, PROC SQL only looks at the columns with the same name. Because of ALL, it does not eliminate duplicates. Then it matches up the numbers in X. Note that this is a one-to-one match, so only one of the 1s from table one is displayed in the output.

28
Q

What set operator is represented in the image?

What two things does it do?

A

UNION

It selects unique rows from both tables

it overlays columns

29
Q

Which set operator is represented in the image?

How does it work?

A

OUTER UNION

It concatenates the results of the queries by selecting all rows (both unique and nonunique) from both tables

It does not overlay columns

30
Q

What happens if you use the CORR keyword with OUTER UNION?

A

It overlays columns that have the same name. Normally, OUTER UNION does not overlay columns

31
Q

What is the DATA step equivalent of this PROC SQL query?

proc sql;

create table three as

select * from one

outer union corr

select * from two;

quit;

A

data three;

set one two;

run;

proc print data=three noobs;

run;

See pg. 156

Data step does something similar to OUTER UNION but it also automatically overlays same-named columns. You have to add corr to OUTER UNION for it to do that.

32
Q

For the set operators EXCEPT, INTERSECT, and UNION, columns are overlaid based on ____________ in the SELECT clause rather than by column name. In order to be overlaid, columns in the same relative position in the two SELECT clauses must have ________________.

A

For the set operators EXCEPT, INTERSECT, and UNION, columns are overlaid based on the relative position of the columns in the SELECT clause rather than by column name. In order to be overlaid, columns in the same relative position in the two SELECT clauses must have the same data type.

33
Q

For which set operators does PROC SQL makes two passes through the data?

A

For set operators that display only unique rows (EXCEPT, INTERSECT, and UNION)

  1. PROC SQL eliminates duplicate rows in the tables
  2. PROC SQL selects the rows that meet the criteria and overlays columns
34
Q

Create an empty table called “discounts” by defining new columns Destination (character, width 3), BeginDate (num, date9), EndDate (num, date9), and Discount (num)

A

Note can specify width for character but not for numeric columns

proc sql;

create table work.discounts

(Destination char(3) label=’City’,

BeginDate num Format=date9.,

EndDate num format=date9.,

Discount num);

35
Q

What are the two data types used by SAS tables?

What happens if you specify another data type?

A

numeric and character

PROC SQL converts the supported data tytpes that are not SAS data types to either numeric or character format

36
Q

What is the PROC SQL equivalent of PROC CONTENTS?

A

Note that DESCRIBE TABLE writes to the SAS log, while PROC CONTENTS generates a report

DESCRIBE TABLE

It list columns and column attributes. Also gives information about indexes defined on a table.

proc sql;

describe table tablename;

37
Q

Create an empty table that has the same columns and attributes as an existing table but has no rows (it is empty)

Where would you add the drop or keep options if you only want a few columns of the existing table?

A

table1 is the table you want to emulate

proc sql;

create table tablename

like table1;

You can add drop or keep between the name of the table being created and the like clause or after the name of table1.

eg.

proc sql;

create table tablename

(drop = column1 column2)

like table1;

38
Q

Insert rows by using the SET clause - syntax

A

Use one set clause for each row

proc sql;

insert into tablename

set column1 = value-1, column2 = value-2

set column1 = value-3, column2 = value-4;

39
Q

Insert rows by using the VALUES clause - syntax

A

Use one value clause for each row.

proc sql;

insert into tablename (column1, column2, column3)

values (value-1, value-2, value-3)

values (value-4, value-5, value-6);

40
Q

Inserting rows from a query result - syntax

A

proc sql;

insert into tablename

select column1, column2, column3

from othertable;

41
Q

What are integrity constraints?

A

Rules that you can make to restrict the data values that can be stored for a column in a table

42
Q

What are general integrity constraints?

List the 4 general integrity constraints

A

PRIMARY KEY is a general integrity constraint if it does not have any FOREIGN KEY constraints referencing it.

General integrity constraints enable you to restrict the values of columns within a single table.

CHECK, NOT NULL, UNIQUE, PRIMARY KEY

43
Q

When is a referential integrity constraint created?

A

A referential integrity constraint is created when a PRIMARY KEY in one table is referenced by a FOREIGN KEY in another table.

44
Q

Syntax for creating a constraint in a column specification

A

proc sql;

create table tablename

(column1 datatype constraint,

column2 datatype constraint);

e.g.

proc sql;

create table work.employees

(ID char(5) primary key,

Name char(10),

Gender char(1) not null check(gender in (‘M’, ‘F’));

45
Q

Syntax for creating a constraint by using a constraint specification

What are two advantages compared to creating a constraint in a column specification?

A

Insert the actual constraint type in constraint

proc sql;

create table tablename

(column1 datatype,

column2 datatype,

constraint constraint-name constraint(column1),

constraint constrain-name constraint(column2);

  • can specify a name for the constraint
  • can define a constraint for multiple columns
46
Q

When you’re inserting rows to a table that has constraints and one of the rows does not meet the constraints, does PROC SQL

a) insert only the rows that meet the contraints
b) not insert any of the rows

A

B. PROC SQL does not insert any of the rows

47
Q

What is the UNDO_POLICY= option?

Where do you put it in code?

What are the 3 settings for UNDO_POLICY?

A

It controls how PROC SQL handles updated data if any errors occur during the insertion or update process

proc sql undo_policy=option;

Settings:

REQUIRED: Default. Doesn’t add any new rows if any errors are met.

NONE: PROC SQL skips records that cannot be inserted or updated. Any data that meets the integrity constraints is added or updated

OPTIONAL: If UNDO can be done reliably, then it proceeds as if UNDO_POLICY = REQUIRED. Otherwise, it proceeds with UNDO_POLICY=NONE

48
Q

How do you display a table’s integrity constraints?

A

proc sql;

describe table constraints tablename;

49
Q

How do you update values in existing table rows (update all or a subset of rows in a column)?

A

Use the where statement when you want to update only a subset of the rows in the column.

proc sql;

update tablename

set column1 = updateexpression

where expression;

eg.

proc sql;

update payrollmaster

set salary = salary*1.05

where jobcode like ‘__1’;

50
Q

Update values in existing table rows (case method) syntax

A

Here the change we’re making is multiplying column1 by a certain number depending on conditions

proc sql;

update tablename

set column1 = column1*

case

when condition = 1 then answer1

when condition = 2 then answer2

else answer3

end;

case condition

when 1 then answer1

when 2 then answer2

else answer 3

end;

(See pg. 206)

51
Q

In which 3 PROC SQL statements can you use a CASE expression?

A

UPDATE, INSERT, and SELECT

52
Q

How do you create a new column with the case clause?

A

proc sql;

select column1, column2,

case condition

when 1 then answer1

when 2 then answer2

else answer3

end as newcolumn

from table1, table2

where table1.column1 = table2.column1;

53
Q

How do you delete rows from a table?

A

proc sql;

delete from tablename

where condition;

54
Q

Syntax for altering columns in a table

A

proc sql;

alter table tablename

add newcolumn datatype, newcolumn2 datatype

drop column1

modify column2 datatype(width) format = format label=’label’;

55
Q

What things can you change with a MODIFY clause?

What can you NOT do with a MODIFY clause?

A

You can change a column’s length (width) for character variables, informat, format, and label

You cannot change the data type of a column, and you cannot change the column’s name

56
Q

How do you drop a table?

A

proc sql;

drop table tablename;

SAS Advanced Cert Exam (6 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions