Advanced cert study

Question 1

proc fcmp

Answer 1

outlib arument is required*
PROC FCMP OUTLIB =libname.dataset.package;

FUNCTION name (parameter-1, …,
parameter-N);
program-statements;
RETURN (expression);
ENDSUB;
run;

Question 2

macro loops

Answer 2

%do i=1 %to
%end
works with %do %until and %do %while

Question 3

subroutine (proc fcmp cont)

Answer 3

SUBROUTINE subroutine-name (argument-1,…, argument-n);
OUTARGS out-argument-1, …, out-argument-n;
… more-program-statements …
ENDSUB;

Question 4

%str

Answer 4

masks the normal meaning of these tokens: + - * / , < > = ; ‘ “ %NRSTR also mask macro call values

Question 5

scan function

Answer 5

SCAN(string,n)

breaks up a string into words that can be returned as the value. returns the nth word;

Question 6

proc sql logic

Answer 6

case
when-then
when then 
else 
end as var

Question 7

symputx and symget

Answer 7

call symputx(‘var’, value) symget(‘var’)

Question 8

find function

Answer 8

FIND(string,tgtsubstring)
searches a string for a certain substring returns the position of the first occurence of the substring within the string if found , else 0

Findc(string, charlist, )
returns first occurence of any char else 0

FINDW(string,word);

Question 9

copying tables

Answer 9

create table tablename
like oldtable: creates an empty table

Create table tablename as query expression; creates table from a query

Question 10

All and corr

Answer 10

all does not remove matching rows

corr remove nonmatching columns

Question 11

%scan

Answer 11

%SCAN(argument, n )

returns the nth word of an argument

Question 12

Union

Answer 12

select *
from one
union
select *
from two;

UNION
Unique rows from both tables are selected with columns
overlaid.

Question 13

select distinct

Answer 13

eliminates duplicate rows in query results

Question 14

Hash objects

Answer 14

data mem_type;
length Code $2 MemberType $40;
if _N_=1 then do;
declare hash T();
T.definekey('key');
T.definedata('MemberType');
T.definedone();
end;
set orion.europe_customers;
T.find(); * matches vars with same value otherwise use
t.find(key: 'var');

Question 15

%Eval

Answer 15

%EVAL(expression)

performs arithmetic ops, truncates non int restults, returns 1 or 0 for logical ops, retuns a null value for non-int vals

Question 16

%local

Answer 16

delcares macro values within a macro function, like a %let statement

Question 17

inner and outer joins

Answer 17

inner return rows with matching keys, up to 256

outer rows return all rows with matching and nonmatching keys, only can be two at a time

Question 18

arrays

Answer 18

ARRAY array-name {number-of-elements}
;

2 dim array
ARRAY array-name {…,rows, cols}
;

Question 19

proc append

Answer 19

proc append
base=basetable
data=appendtable
; 
quit;

Question 20

except

Answer 20

select *
from one
except
select *
from two;

Unique rows from the first table that are not found in
the second table are selected.

Question 21

proc sql contents equi

Answer 21

proc sql:

describe table;

Question 22

Intersect

Answer 22

select *
from one
intersect
select *
from two;
Common unique rows from both tables are selected.

Question 23

set operators

Answer 23

SELECT …
EXCEPT | INTERSECT | UNION | OUTER UNION
SELECT …

Question 24

where v having clause

Answer 24

The WHERE clause is processed before a GROUP
BY clause and determines which individual rows are
available for grouping.

The HAVING clause is processed after the GROUP
BY clause and determines which groups will be
displayed.

Question 25

outer join syntax

Answer 25

``` SELECT column-1 FROM table-1 LEFT|RIGHT|FULL JOIN table-2 ON join-condition(s) ; ``` left and right are positional

Question 26

colesce

Answer 26

COALESCE(argument-1,argument-2

Question 27

%global

Answer 27

adds macro variables to the global symbol table with null values

Question 28

%substr

Answer 28

%SUBSTR(argument, position ) returns portion of argument begining at position for a length of n chars, of no end takes rest of string if quotations are a part of string they are counted as chars

Question 29

proc sql query

Answer 29

6 SELECT column-1 FROM table-1|view-1 > >;

Question 30

proc format picture

Answer 30

proc format; picture US_Phone low-high='9 (999) 999-9999'; (prefix =' '); picture AU_Phone low-high='99 (9) 9999-9999'; (prefix =' '); run; 9 indicates no triming leading 0s, 0 indicate trim

Question 31

%sysfunc

Answer 31

allows the use of most sas functions in a macro environemt