Reading Other Data File Types

Question 1

How do you access an excel file in SAS?

Answer 1

Use SAS/ACCESS LIBNAME statement. Then SAS treats each worksheet in the workbook as a SAS data set.

Question 2

SAS/ACCESS LIBNAME statment

Answer 2

LIBNAME libref (engine) (PATH=) “workbook file path” (options);

Example:

LIBNAME orionx pcfiles path=”&path/sales.xls”;

(PATH= is used with pcfiles engine)

Question 3

How do you determine which engine to use in SAS/ACCESS LIBNAME statement?

Answer 3

SAS and MS Office offer 32-bit and 64-bit.

If bitness is the same, use excel engine.

If bitness is not the same, use pcfiles engine.

Question 4

What is the SAS name literal?

Answer 4

libref.’worksheetname$‘n

It’s a way to allow special characters or blanks in data set names. Useful since excel files are displayed in SAS with special character $.

Question 5

Why is it important to dissociate a libref given to an excel file?

Answer 5

SAS locks the excel document while libref is assigned to it, and you won’t be able to open in in excel.

Question 6

How do you access data from a database?

Answer 6

Use SAS/ACCESS LIBNAME statement:

LIBNAME libref engine (SAS/ACCESS options);

engine is the database (e.g. oracle)

SAS/ACCESS options:

USER=

PASSWORD=

PATH=

SCHEMA=

Question 7

What are 3 types of raw data files?

Answer 7

text file, CSV file, and ASCII file

Question 8

What are 2 ways data can be presented in a raw data file?

Answer 8

Delimited file: Data values arranged sequentially. Data values separated by spaces or other special characters

Fixed column file: arranged in columns. Each field has a set width in every record.

Question 9

What important information about each record do you need to specify to read a delimited raw data file in SAS?

Answer 9

Location of data value in the record, name of SAS variable in which to store the data, and the type of SAS variable

Question 10

What are the 3 different techniques to read raw data files in SAS?

Answer 10

1. List input: reads standard and nonstandard data separated by delimiter
2. Column input: reads standard data arranged in columns
3. Formatted input: reads standard and nonstandard data arranged in colums

Question 11

What is the difference between standard and nonstandard data?

Answer 11

SAS can read standard data without any special intructions.

Nonstandard data can include dates or values with special characters. SAS needs extra intructions to read this data.

Question 12

How do you use the DATA Step to read raw data?

Answer 12

DATA new-data-set;

INFILE “file path” dlm=’,’;

INPUT variable1 variable2 $ variable3 $;

RUN;

dlm = what separates the data values. The default is a space or blank

INPUT specifies variables in order in which they appear in the raw data file. Character variables are followed by $

Question 13

During compilation phase, what does SAS create that is particular to raw data files?

Answer 13

SAS creates an input buffer before the PDV. SAS reads data from the raw data file into the input buffer and the PDV reads from the input buffer.

It looks like a ruled grid where each character in the data value is placed in a different square

Question 14

How do you specify the length of certain variables?

Answer 14

Use the LENGTH statement

LENGTH variable(s) ($) length;

Note: This needs to go before the input statement.

Note: The variables will be listed in the order and case used in the length statement.

Question 15

What is the difference between formats and informats?

Answer 15

Formats tell SAS how to write a value.

Informats tell SAS how to read a valued.

Question 16

INPUT statement for a modified list input

Answer 16

INPUT variable ($) variable (:informat.);

: tells SAS to read up to the delimiter

Question 17

Informat statement

Answer 17

($)informat(width).

Question 18

Colon format modifier, e.g. :date.

Answer 18

Tells SAS to read up to delimiter. Ignores width.

Question 19

How to add own data straight in the program

Answer 19

DATALINES statement

DATA work.mydata;

input variable1 variable2;

datalines;

(my data)

;

Note: datalines and the data should be the last thing on the DATA step

Note2: this doesn’t need to end in “run”

Question 20

If using DATALINES statement, where do you add the delimiter statement?

Answer 20

data work.mydata;

infile datalines dlm=’,’;

input variable1 variable2;

datalines;

(my data)

;

Question 21

DSD option - what is it and when to use

Answer 21

DSD = delimiter sensitive data

INFILE ‘file path’ (DLM=) DSD;

• Sets default delimiter to a comma
• treats consecutive delimiters as missing values
• enables SAS to read values with embedded delimiters if the value is surrounded by quotation marks

Use it when have missing values in raw data file in list style

Question 22

MISSOVER Option - what is it and when to use it

Answer 22

INFILE ‘file path’ DLM= ‘,’ MISSOVER;

Use when there’s a missing value at the end of a record. This option sets the value to missing. Note that DSD would not work if missing value is at end of the line because it would not be between delimiters.

Question 23

True or False:

You can use the WHERE statement to subset observations read from a raw data file

Answer 23

FALSE

Question 24

When using list input, what is the default length for all variables?

Answer 24

8 bytes

Question 25

What do you use to read nonstandard data from a raw file?

Answer 25

Modified list input

Question 26

How does list input treat two or more consecutive delimiters (i.e. when there’s a missing value)?

Answer 26

It treats two or more delimiters as a single delimiter and not as a missing value. In this case, need DSD option.

Question 27

General form of Formatted Input

Answer 27

INPUT (pointer-control) variable1 informat.;

pointer-controls:

1. @n moves the input pointer to a specific column number
2. +n moves the input pointer forward to a column number that is relative to the current position

Question 28

How to read variable-length records that contain fixed-column data?

Answer 28

Use the PAD options in INFILE statement. It pads each record with blanks so that all data lines have the same length

Question 29

In modified list input, how do you read character values that contain embedded blanks? e.g Los Angeles

Answer 29

Use & modifier

data dataset;

infile filename;

length City $12;

input Rank City &;

run;