SAS Advanced Programming

local

%LET inside a macro will assign a variable to the ___ symbol table

True

A Perl regular expression within the PRXMATCH function must start and end with a delimeter such as a forward slash

Cartesian

A ___ product is where each row in the first table is combined with every row in the second table

True

An array is not an executable statement, it merely defines an array. True or False?

Yes

Are dictionary tables read-only?

numeric, 8

By default, array columns are created as what format and what length?

Yes

Can a macro definition include both macro language statements AND SAS language statements?

Yes

Can autocall libraries be concatenated together?

No

Can the INTO Clause be used in a subquery or when creating a table?

Yes

Can the autocall facility be used with the stored compiled macro facility?

No

Can you use %LET to define a global macro variable inside a macro?

declare hash ContName(MULTIDATA:'YES');

Create a hash object ContName that allows duplicate key components

declare hash States();

Create hash object States without any arguments or tag values.

0

DOSUBL returns a value of ___ if SAS code was able to execute

default=10

Fill in the blank to increase rhe length to 10: proc format; picture mydate (______) low-high='%0d-%3b%Y' (datatype=date); run;

COUNTC

Function that counts the number of characters in a string that appear or do not appear in a list of characters

COUNT

Function that counts the number of times that a specified substring appears within a character string

COUNTW

Function that counts the number of words in a character string

DATA

Hash objects can only be used in a ___ step and is only available for the duration of that step

/*

How would you start a comment within a macro?

local

If a macro variable exists both in the global symbol table and in the local symbol table, the macro processor uses the ___ variable in cases where the variable is being referenced

first

If a query is used to store a value in a macro variable and the query produces multiple values, only the ___ value is stored in the macro variable

input stack

In macro execution, the macro processor places SAS language statements (such as a PROC PRINT in a macro) to the ___ ___

macro processor

In macro execution, when the word scanner encounters a macro variable reference such as &SYSLAST, it passes the rererence to the ___ ___ for resolution

ALL

In using set operators, use the ___ keyword if you are using EXCEPT and want to select all rows in the first table even if they are duplicates, as long as they don't have a matching row in the second table

No

Is a semicolon required when calling a macro?

No

Is an array name included in the PDV?

before

Macro functions and variable references are passed to the macro processor ___ (before or after?) the program is compiled/SAS language statements in the DATA step are executed

character

Macro variables are stored as ___ strings, so quotation marks aren't needed and would be part of the value if includef

DATALINES

Macro variables can be defined and referenced anywhere in a SAS program except in a ____ statement

positional, keyword

Macros can take two types of parameters: ___ and ____.

CORR

OUTER UNION will not overlay columns unless you use the ___ keyword

null

Positional parameters have a default value of ___

libname market oracle user=user password=student path=localhost schema=Analyst;

Set up a FEDSQL libname market, with user as the user, student as the password, and Analyst as the schema

no

Should you use the TRIMMED keyword when already using the SEPARATED BY keyword?

OUTER UNION

The ALL keyword cannot be used with which set operator?

DOSUBL

The ___ function enables the immediate execution of SAS code after a text string is passed, used in a DATA step for data-driven macro calls

MCOMPILENOTE=

The ____ system option writes a note to the SAS log when a macro has completed compilation; otherwise no note is written

word scanner

The presence of text in the input stack triggers a component called the ___ ___ to begin its work

single

The word scanner does not recognize macro triggers (%, &) that are enclosed in ___ quotation marks

NMISS

To count the number of missing values, use the ___ function

%NRSTR

To hide the normal meaning of an ampersand or a percent sign, use the ___ macro function

Monthly.find(key:CityName,key:MonthName);

Use a hash object Monthly to copy the data for the CityName and MonthName keys

SOUNDEX

What algorithm does the Sounds-like operator use?

ALL, CORR

What are the 2 optional keyworda after a set operator?

PRXMATCH, PRXCHANGE, PRXPARSE

What are the 3 Perl functions?

name, number, special, literal

What are the 4 tokens?

Limit

What clause would you use to specify the number of rows that a FEDSQL query returns? Equivalent to INOBS and OUTOBS in Proc sql

1

What does %eval(5/3) return?

Database Management System

What does DBMS stand for?

PRXMATCH

What function searches a string for a pattern match and returns the position of the substring at which it starts?

%

What is the wildcard character for any sequence of 0 or more characters?

_

What is the wildcard character for any single character?

return, endsub

What two statements do you need at the end of a custom function?

0

What value will the FIND function return if the key value is found in a hash object

Joan's report

What will %let text=%str(Joan%'s Report) return?

Years1

What will the first variable be, produced from the array years[2011:2016];

's

What would you start a PRXCHANGE with after ( to signify that substitution needs to happen instead of matching

True

When PROC SQL remerges data, it displays a related message in the SAS log

SASMACR

When processing a macro, all compiled macro language statements and constant text are stored in a SAS catalog entry if no syntax errors are found. By default, that catalog is Work.____, with an entry named the name of the macro

SYMBOLGEN

When the ____ option is turned on, SAS writes a message to the log for each macro variable that is referenced in the program

input stack

When you submit a SAS program, the code is copied to a memory location called the ___ ___

execution

Whereas an array is defined at compile time, a hash object is defined at ___

COALESCE

Which function can be used to overlay columns in a PROC sql join?

_ALL_

Which optional argument would you use with a %PUT statement to list the values of all macro variables?

INTERSECT

Which set operator is first in order of operations?

Positional

Which type of parameter must always be listed first when creating a macro with mixed parameters?

first

With set operators for 2 tables, when columns are overlaid, PROC SQL uses the column name from the ___ table

bar\b

Write a Perl expression that matches "bar food" but not "barfood"

d[^a]me

Write a Perl expression that matches "dime" or "dome" but not "dame"

bar\B

Write a Perl expression that matches "foobar" but not "bar food"

ter$

Write a Perl expression that matches "winter" but not "winner" or "terminal"

zo*

Write a Perl expression that matches "zo" and "zoo"

zo+

Write a Perl expression that matches "zo", "zoo", but not "z"

mi.e

Write a Perl expression that matches both "mike" and "mice"

[dmn]ice

Write a Perl expression that matches either "dice", "mice", or "nice"

x\sx

Write a Perl expression that matches with "x x"

\D\D\D\D

Write a Perl expression that matches with any four non-digit string such as "WxYz"

\d\d\d\d

Write a Perl expression that matches with any four-digit string such as "1234"

options mautosource sasautos=('C:\mysasfiles', sasautos);

Write code to access macros in the autocall library C:\mysasfiles.

proc sql; insert into discount (destination, discount) values ('ORD', .25) values ('YYZ', .10); quit;

Write code to add 2 new rows to the discount dataset, with destination ORD and a discount of .25, and YYZ with a discount of .10 using VALUES clause

Airports.add(key: 91, data: "America");

Write code to add 91 as the key and America as the data, to the Airports hash object

proc sql; insert into discount set destination='LHR', discount=.33; quit;

Write code to add a new row to the discount dataset, with destination LHR and a discount of .33, using SET statement

libname test clear;

Write code to close the DBMS connection that you set up using the SAS/ACCESS LIBNAME engine, with the LIBNAME test

proc sql; select *; from one, two; quit;

Write code to complete a Cartesian product of tables One and Two

proc sql; connect to oracle (user=User, password=123, path=localhost); select * from connection to oracle (select * from customers); disconnect from oracle; quit;

Write code to connect to Oracle and query all of the customers table. The username is User and the password is 123.

array fun[4,2](row, column);

Write code to create a 2D array fun that is 4 by 2. Write the generic syntax for loading in the first set of values

%local dsn;

Write code to create a local macro variable dsn

%let d=%eval(10+20);

Write code to create a macro variable d using EVAL to sum 10.0 and 20.0

proc sql; create table flightdelays2 like flightdelays; quit;

Write code to create a table flightdelays2 containing the aame columns and attributes as flightdelays; but with no rows.

array health[5] Weight--BP

Write code to create an array health with 5 elements with all columns between Weight and BP

call symputx('crsname', course_title);

Write code to create the macro varibale crsname from the course_title variable

rc=Airports.find(key:Abbr)

Write code to define a variable rc equal to the result of retrieving the value of Code from the Airports hash object based on the key Abbr.

declare hiter C('customer');

Write code to define the hash iterator object, C, which points to the hash object, Customer

%SYMDEL Cartype;

Write code to delete the Cartype macro variable from the macro global symbol table.

proc sql; drop view certadv.raisev; quit;

Write code to delete the certadv raisev view

proc sql; describe table discount; quit;

Write code to display a list of columns and column attributes for the discount table (not PROC CONTENTS)

proc sql; insert into level3 select empid, salary from level2 where empid='1653'; quit;

Write code to insert a new row into level3 containing the empid and salary from level2 where the empid is '1653'.

%include 'C:\Users\James\fun.sas' /source2;

Write code to insert the statements of a macro program fun.sas stored at 'C:\Users\James\fun.sas'. Make sure the SAS statements of the macro are displayed in the SAS log.

function fun(dog $, cat $) $40;

Write code to make a custom function fun that takes 2 character arguments, dog and cat. The value returned should be character and length 40

declare hash airports(dataset: "ctcities"); airports.definekey("Code"); airports.definedata("City", "Name"); airports.definedone();

Write code to make an airports hash object, from the ctcities dataset. Use Code as the key, City and Name as the data. Load the dataset into the hash object.

array Ordt[*] Ordt:;

Write code to make an array Ordt that contains all columns starting with Ordt.

proc sql; select * from schedule natural join courses; quit;

Write code to naturally join schedule and courses, selecting all.

array quota[5] _temporary_;

Write code to produce a temporary array quota with 5 elements. The columns will be automatically eliminated at the end without a DROP statement

data _null_; set certadv.FlightCrewNew; rc=dosubl(cats('%DelayReport(',empid,')')); run;

Write code to run the DelayReport macro with a data driven macro call, in a DATA step _null_, using certadv.FlightCrewNew based on the empid

proc sql noexec inobs=5 number; select * from dog; quit;

Write code to select only 5 rows, and all columns, from the dog table, and include a column named Row that displays row numbers. Choose not to run the query but to only validate kit.

proc sql; select address from frequentflyers where address like "%P%PLACE"; quit;

Write code to select rows from frequentflyers where address street name begins with P and ends with the word PLACE

ContName.output(dataset: 'work.contname'):

Write code to send the data in the ContName hash object to a dataset named contname

options cmplib=certadv.functions;

Write code to specify the option needed to search for the Certadv.Functioms table that contains a custom function

proc sql; select avg(Salary) into:avgSal trimmed from payroll; quit;

Write code to store the average salary in the payroll table in a macro variable avgSal. Ensure leading and trailing blanks are removed

proc fcmp outlib=certadv.funcs.dev

Write code to store the custom function in the Dev package within the SAS table Certadv.funcs

lastname =* "Smith";

Write code to use the sounds-like operator to select any lastname that sounds like Smith

options mlogic; %fun;

Write code to write messages to the SAS log that show the beginning through the end of macro processing and call the macro FUN

proc sql feedback; select * from certadv.staffchanges; quit;

Write code to write the expanded list of all columns to the SAS log, from the certadv.staffchanges dataset

proc fedsql; select put(Sales1, dollar10.2) as Sales1 from sales; quit;

Write fedsql code to select Sales1 from sales and format it dollar10.2, keeping the same column name

proc sql; select * from table1 except/intersect/union/outer union select * from table2; quit;

Write generic code to use set operator to select data from table1 and table2

autocall

___ libraries are either directories that contain source files, or SAS catalogs that allow the macro processor to search the library for the macro, compile and store it as if it had been submitted, and then execute it

%STSEVALF

____ is the only macro function that can evaluate logical expressions that contain floating-point or missing values

No

by default, Does PROC SQL overlay columns with the same name in a join?

CORR

in using set operators, use the ___ keyword if the two tables have some columns in common, hut are not in the same order