Lesson 2: Accessing Data

Question 1

Syntax to view / readtable and column a
attributes

Answer 1

PROC CONTENTS DATA=data-set;
RUN;

Question 2

Syntax to create a Library

Answer 2

LIBNAME - libref - engine - “path”;

LIBNAME - key word
libref - name of your library
engine - (base is default, can be omitted)
path - found in Properties

Question 3

Syntax to delete library Ref

Answer 3

LIBNAME libref CLEAR;

libref if your LIBRARYNAME

Question 4

Syntax to use PROC step with Library Ref

Answer 4

PROC CONTENTS DATA = LIBRARYNAME.FILENAME;
RUN;

Question 5

What kind of statement is LIBNAME? Does it need a RUN statement?

Answer 5

LIBNAME statement is a global statement and does not need a RUN statement at the end.

Question 6

What are LIBNAME name attributes?

Answer 6

• The libref must be 8 characters or less
• must start with either a letter or underscore
• includes only letters, numbers, and underscores
• the path must also be in quotation marks

Question 7

How to access tables (data) in your library?

What is the syntax?

Answer 7

PROC CONTENTS DATA = libref.table-name

You use the library to access tables in your program by specifying
- the libref (or library name)
- a period and
- the table name (mylib.class).

You don’t have to type the file extension because SAS already knows you’re accessing SAS tables from that location.

Question 8

Work Library

Answer 8

WORK library is default, automatic and temporary; all tables deleted at the end of the session.

Tables stored in the Work library are deleted at the end of each SAS session.

Work is the default library, so if a table name is provided in the program without a libref, the table will be read from or written to the Work library.

DATA=WORK.TEST
DATA=TEST

Question 9

SASHELP Library

Answer 9

SASHELP is automatic and contains collection of sample tables and other data.

The library is automatically available when SAS starts.

DATA=SASHELP.CARS

Question 10

Syntax to use library to READ data directly from Excel workbooks?

Answer 10

LIBNAME libref XLSX “path/file-name.xlsx”;

LIBNAME - keyword
libref - name of your library
XLSX - engine to READ Excel

The full path to the Excel file, including the .xlsx file extension, must be provided in DOUBLE quotation marks.

Question 11

Importing unstructured data

Answer 11

You must import the data into a structured format, such as a SAS table, before you can use the data in a program. SAS can import delimited files with any character acting as the delimiter. By default, SAS assumes column names are found in the first row of the file.

PROC IMPORT
DATAFILE=”path/filename” DBMS=filetype
OUT=output-table
REPLACE
GUESSINGROWS=n|MAX;
RUN;

PROC IMPORT reads data from an external data source and writes it to a SAS data set.

DATAFILE= path and complete file name

DBMS= defines the file type (e.g. cvs, txt)

OUT= library and name of the SAS output table

REPLACE overwrites the SAS output table if it already exists

GUESSINGROWS=provides a number of rows to examine. By default it scans the first 20 rows

Question 12

Importing Excel Files

Answer 12

Imports a copy of Excel data as a SAS table and uses that SAS table in your program.

PROC IMPORT can read only one spreadsheet at a time, and by default it reads the first worksheet.

PROC IMPORT DATAFILE=”path/file-name.xlsx”
DBMS=XLSX
OUT=output-table <REPLACE>;
SHEET=sheet-name;
RUN;</REPLACE>

Question 14

What portions do SAS data sets have?

Answer 14

DATA and DESCRIPTOR

Question 15

What are the three types of SAS columns?

Answer 15

Name, Type, and Length.

Question 16

Columns are either

Answer 16

character or numeric

Question 17

Character columns can have a length

Answer 17

between 1 and 32,767 bytes (1 byte = 1 character).

Question 18

Numeric columns are stored with a length of

Answer 18

8 bytes.

Question 19

Character columns can consist

Answer 19

letters, numbers, special characters, or blanks.

Question 20

Numeric columns can consist of

Answer 20

digits 0-9, minus sign, decimal point, and E for scientific notation.

Question 21

SAS date values are a type of numeric value and represent the number of days between

Answer 21

January 1, 1960, and a specified date

Question 22

What is LIBREF

Answer 22

is the name of the library that can be used in a SAS program to read data files.

Question 23

What does ENGINE do?

Answer 23

provides instructions for reading SAS files and other types of files.

Question 24

What does PATH do?

Answer 24

provides the directory where the collection of tables is located.

Question 25

The LIBREF remains active until

Answer 25

you clear it, delete it, or shut down SAS.

LIBNAME libref engine “path”;
LIBNAME libref CLEAR;

Question 26

The XLSX engine enables

Answer 26

to read data directly from Excel workbooks. The XLSX engine requires the SAS/ACCESS to PC Files license.

Question 27

The VALIDVARNAME=V7 system option forces

Answer 27

table and column names read from Excel to adhere to recommended SAS naming conventions.

Spaces and special symbols are replaced with underscores, and names greater than 32 characters are truncated.

Question 28

Date values are automatically converted

Answer 28

to numeric SAS date values and formatted for easy interpretation

Question 29

Worksheets or named ranges from the Excel workbook can be referenced in a SAS program as

Answer 29

libref.spreadsheet-name.

Question 30

other databases, it’s a good practice to delete the libref at the end of your program with WHAT option?

Answer 30

CLEAR

OPTIONS VALIDVARNAME=V7;
LIBNAME libref XLSX “path/file.xlsx”;

Question 31

The DBMS option identifies

Answer 31

the file type.
The CSV value is included with Base SAS.

Question 32

The OUT= option provides

Answer 32

the library and name of the SAS output table

Question 33

The REPLACE option is necessary to

Answer 33

overwrite the SAS output table if it exists.

Question 34

SAS assumes that column names are in the

Answer 34

1st line of the text file and data begins on the second line

Question 35

Date values are automatically converted to

Answer 35

numeric SAS date values and formatted for easy interpretation

Question 36

The GUESSINGROWS= option indicates the

Answer 36

number of rows the IMPORT procedure scans in the input file to determine the appropriate data type and length of columns. The default value is 20 and the allowed range is 1 to 2147483647 (or MAX).

Question 37

Syntax for importing CSV file is

Answer 37

PROC IMPORT DATAFILE=”file.csv”
DBMS=CSV
OUT=output-table REPLACE;
GUESSINGROWS=n;
RUN;

Question 38

Syntax for Importing an Excel (XLSX) File

Answer 38

PROC IMPORT DATAFILE=”file.xlsx” DBMS=XLSX
OUT=output-table REPLACE;
SHEET=sheet-name;
RUN;

Question 39

Missing numeric values are represented

Answer 39

with a period