Phonetic Matching (=*) & Pattern Matching (% and _)

Phonetic matching (Sounds-Like Operator =*)
e.g.
where custname =* 'Lafler';

Finding patterns in a string (Pattern matching % and _)
e.g.
where prodtype like '%soft%';
where prodtype like '____a%';

LIKE Clause in CREATE TABLE

The LIKE clause tiggers the existing table's structure to be copied to the new table minus any column dropped with the KEEP= or DROP= option.

proc sql;
      create table hot_products
      like products;
quit;

LOG Results:
NOTE: Table HOT_PRODUCTS created, with 0 rows and 5 columns.

Call Execute

Data step call routine
call execute('%sales');
call execute('%sales('||month||')');
The following DATA step uses CALL EXECUTE to execute a macro only if the DATA step writes at least one observation to the temporary data set.

%macro overdue;
proc print data=late;
title "Overdue Accounts As of &sysdate";
run;
%mend overdue;

data late;
set sasuser.billed end=final;
if datedue<=today()-30 then
do;
n+1;
output;
end;
if final and n then call execute('%overdue');
run;

SYSPARM

Conditionally Execute Batch Jobs

data one;
      set one;
      if scan(sysparm(),1) ^= ' ' then do;
......

data _null_;
      array p{3} p1-p3;
      inp=sysparm();
      if inp='' then abort abend;
      do i = 1 to 3;
        p[i]=scan(inp,i);
      end;
      call symput('v1',left(trim(p1)));
......


sas program.sas -sysparm "XYZ ABC OPQ"

MOD function

MOD (dividend-expression, divisor-expression)

Returns the remainder from the division of dividend-expression by divisor-expression.

Statements	Results
a=mod(10,3);	1
a=mod(.35,-.1);	0.05

Sample program: (select certain observations from a dataset)

data temp;
       set temp;
       if mod(_N_,3)=0;
run;

Options VALIDVARNAME=ANY

VALIDVARNAME=V7 V6 UPCASE ANY

V7 - (default) indicates that up to 32 mixed case alphanumeric characters are allowed. Names must begin with alphabetic characters or an underscore.

V6 - only 8 bytes long.

UPCASE - variable names are uppercased.

ANY - allows any characters to appear as valid SAS variable names. Symbols, such as "=" and "*", must be contained in a 'varname'n construct.
e.g.
libname foo ......;

data foo.'My Table'n;

input 'Amount Budgeted'n 'Amount Spent'n 'Amount Difference'n;

Calculate Age

%macro age(date,birth);

floor((intck('month',&birth,&date)
    - (day(&date) < day(&birth))) / 12);

%mend age;

Compress

Strip off some typical special characters:

test2=compress(wordvb, '0D'x);
*** remove the carriage return;

test2=compress(test2, '0A'x);
*** remove the line feed;

test2=compress(test2, 'A0'x);
*** remove non-breaking space;

varname = compress(varname, , 'kw');
*** The modifier “k” stands for ‘KEEP’ and the modifier “w” stands for ‘WRITABLE’. Note that there is no second parameter in the above code. When compress function is used in combination of K & W modifiers, it keeps all the writable characters which means it deletes all the non writable characters.

Case Logic Versus COALESCE Expression

PROC SQL;
      SELECT CUSTNAME,
        CASE
          WHEN CUSTCITY IS NOT NULL THEN CUSTCITY
          ELSE 'Unknown'
        END AS Customer_City
      FROM CUSTOMERS;
QUIT;

PROC SQL;
      SELECT CUSTNAME,
        COALESCE(CUSTCITY, 'Unknown')
        AS Customer_City
      FROM CUSTOMERS;
QUIT;

ANSI Standard

The ANSI standard reserves a number of SQL keywords from being used as column names. If a column name conflicts with a reserved word,

PROC SQL DQUOTE=ANSI;
      SELECT *
      FROM RESERVED_WORDS
      WHERE "WHERE"='EXAMPLE";
QUIT;

Using Macros to Skip a Section of Code

Just put a “%macro name” at the top and a “%mend” at the end and never call the macro. This effectively comments out the code without running it. No need to worry about other comment styles in the code.

I. Statement Can Be Repeated Or Nested

SAS statements you want to execute.....
%macro SKIP ;
      SAS statements you want to skip.....
%mend SKIP ;

SAS statements you want to execute.....

%macro SKIP ;
      More SAS statements you want to skip.....
%mend SKIP ;
SAS statements you want to execute.....

II. Use NOSOURCE Option

Options Nosource;
%macro SKIP ;
      SAS statements you want to skip.....
%mend SKIP ;

Options Source;
SAS statements you want to execute.....