1

SubQuery

ACCESS

Type the following in an Access query design criteria:
In (Select CaseNo From tblRunThese WHERE RecNo Is Not Null)

You would use this in a situation where you want to limit some records to a list of case numbers in another table (tblRunThese), without having to include that table in a query.

2

Combining all records from multiple tables, even if this final unionized query will result in multiple identical records.

ACCESS

SELECT *
FROM [tblA]
UNION ALL SELECT *
FROM [tblB]
UNION ALL SELECT *
FROM [tblC];

There are two key points here. The asterisk will combine all fields in each record/table. The "ALL" will include all records, even if it results in a duplicate in the resulting union.

3

Combining all records from multiple tables, but limiting this final unionized query to only unique records, thus eliminating any duplicates.

ACCESS

SELECT *
FROM [tblA]
UNION SELECT *
FROM [tblB]
UNION SELECT *
FROM [tblC];

There are two key points here. The asterisk will combine all fields in each record/table. The removal of the "ALL" will exclude any records that are duplicates, thus resulting in only unique records.

4

Combining multiple tables, but only a field named "Topic" from each of the tables, and also limiting this final unionized query to unique records, thus eliminating any duplicates.

ACCESS

SELECT Topic
FROM [tblA]
UNION SELECT Topic
FROM [tblB]
UNION SELECT Topic
FROM [tblC];

There are two key points here. I am not using the asterisk, but will list the specific field that I want. Also, the removal of the "ALL" will exclude any records that are duplicates, thus resulting in only unique records.

5

6

7

8

9

Delete all records from table.

SAS

PROC SQL;
DELETE FROM LIBNAME.TABLENAME;
QUIT;

LIBNAME is the name of the Libname.

10

Insert records into table.

SAS

PROC SQL;
INSERT INTO LIBNAME.TABLENAME
SET ST='CA',
STATE='CALIFORNIA
SET ST='AZ',
STATE='ARIZONA
SET ST='FL',
STATE='FLORIDA';
QUIT;

LIBNAME is the name of the Libname.

11

Create table (TBL_NEW) from another table (TBL_OLD).

SAS

PROC SQL;
CREATE TABLE LIBNAME.TBL_NEW AS
SELECT PERSON AS OWNER, STREET AS ADDRESS, ZIP
FROM LIBNAME.TBL_OLD;
QUIT;

LIBNAME is the name of the Libname.

12

Running a simple query (Single Table)

SAS

PROC SQL;
SELECT *
FROM LIBNAME.TBL_NAME;
QUIT;

LIBNAME is the name of the Libname.

13

Running a simple query
(Inner Join - Method 1)

SAS

PROC SQL;
SELECT TABLE1.STATE, TABLE2.REGION
FROM LIBNAME.TABLE1 INNER JOIN LIBNAME.TABLE2
ON (TABLE1.STATE = TABLE2.STATE);
QUIT;

LIBNAME is the name of the Libname. Note that the Libname is used ONLY after the FROM clause. If you place it in the SELECT or ON clause, you will get an error.

14

Running a simple query
(Inner Join - Method 2 - Variables)

PROC SQL;
SELECT A.STATE, B.REGION
FROM LIBNAME.TABLE1 A INNER JOIN LIBNAME.TABLE2 B
ON (A.STATE=B.STATE);
QUIT;

LIBNAME is the name of the Libname. Just like Method 1, notice that you have to place the Libname in the FROM clause, but not anywhere else.

15

Select the record with the lowest value (RecNo), displaying the entire record.

ACCESS

SELECT *
FROM tblUniverse
WHERE (((tblUniverse.[RecNo])=(SELECT MIN(RecNo)
FROM tblUniverse
WHERE Status IS NULL)));

16

Display records that correspond to the lastest date for a group of people.

ACCESS

SELECT A.Person AS Person, A.Value1 AS Value1, A.Value2 AS Value2
FROM tblMAIN AS A
WHERE (((A.OpenDt) = (SELECT MAX([OpenDt]) FROM tblMAIN WHERE Person = A.Person)));

In this example, we have a table named tblMAIN with fields for Person, OpenDt,Value1,Value2 & Value 3. We want, for each person, only one record that represents the latest date in the [OpenDt] field.
Note the use of the variable "A".

17

Format a SAS Date/Time field to a Short Date format

SAS

PROC SQL;
SELECT FNAME, LNAME, DOB, DATEPART(DOB) AS BIRTHDAY FORMAT=MMDDYY.
FROM LIBNAME.TABLENAME;
QUIT;

In this example, LIBNAME is the name of the libname. Make sure you put a period (.) after the format type.

18

Format a SAS Date/Time field with a DATEPART function (by itself without any formats)

SAS

PROC SQL;
SELECT DOB, DATEPART(DOB) AS DAYS_AFTER
FROM LIBNAME.TABLENAME;
QUIT;

The DATEPART function, without any additional formatting, will return the number of days between the DOB value, and the date of 1/1/1960.

19

View technical data of a SAS table, including the labels

SAS

PROC CONTENTS
DATA=LIBNAME.TABLENAME;
QUIT;

LIBNAME is the name of the Libname. You can use either "QUIT" or "RUN."

20

Change a label name.

SAS

PROC CONTENTS
DATA=LIBNAME.TABLENAME;
LABEL ALL='USA'
QUIT;

LIBNAME is the name of the Libname. This will change the name of the label that is associated with the variable named "ALL" to "USA."

21

22