Advanced Programming Using SAS 9.4

SAS Certified Professional Exam Guide

中文版本 | ← Back to Home

1 Exam Overview

The SAS Certified Professional: Advanced Programming Using SAS 9.4 exam is designed for experienced SAS programmers who want to demonstrate advanced programming skills.

Exam Code: A00-232

Duration: 2 hours

Questions: 60-65 multiple choice questions

Passing Score: 68%

Prerequisites: SAS Certified Specialist: Base Programming Using SAS 9.4

2 Exam Content Areas

2.1 1. SQL Processing with SAS (35%)

2.1.1 Create Tables and Views

• Use PROC SQL to create tables
• Create views for data access
• Understand table aliases

/* Example: Creating tables with PROC SQL */
PROC SQL;
    CREATE TABLE work.sales_summary AS
    SELECT 
        region,
        product,
        SUM(sales) AS total_sales,
        AVG(price) AS avg_price,
        COUNT(*) AS num_transactions
    FROM work.sales_data
    GROUP BY region, product
    HAVING total_sales > 10000
    ORDER BY total_sales DESC;
QUIT;

/* Creating a view */
PROC SQL;
    CREATE VIEW work.high_value_customers AS
    SELECT customer_id, customer_name, total_purchases
    FROM work.customers
    WHERE total_purchases > 5000;
QUIT;

2.1.2 Query and Subset Data

• Use SELECT, WHERE, and HAVING clauses
• Implement complex filtering logic
• Use subqueries

/* Example: Complex queries */
PROC SQL;
    SELECT a.employee_id,
           a.employee_name,
           a.department,
           b.salary
    FROM work.employees AS a
    INNER JOIN work.salaries AS b
    ON a.employee_id = b.employee_id
    WHERE b.salary > (SELECT AVG(salary) FROM work.salaries)
    ORDER BY b.salary DESC;
QUIT;

2.1.3 Joins

• Inner joins
• Left, right, and full outer joins
• Self joins
• Cross joins

/* Example: Various join types */
PROC SQL;
    /* Inner join */
    SELECT a.*, b.sales
    FROM work.customers AS a
    INNER JOIN work.transactions AS b
    ON a.customer_id = b.customer_id;
    
    /* Left join */
    SELECT a.*, b.sales
    FROM work.customers AS a
    LEFT JOIN work.transactions AS b
    ON a.customer_id = b.customer_id;
    
    /* Self join */
    SELECT a.employee_name AS employee,
           b.employee_name AS manager
    FROM work.employees AS a
    LEFT JOIN work.employees AS b
    ON a.manager_id = b.employee_id;
QUIT;

2.2 2. Macro Processing (25%)

2.2.1 Create and Use Macro Variables

• Create macro variables with %LET
• Use automatic macro variables
• Understand macro variable scope

/* Example: Macro variables */
%LET year = 2024;
%LET region = 'East';

PROC PRINT DATA=work.sales;
    WHERE year = &year AND region = &region;
    TITLE "Sales Report for &region Region in &year";
RUN;

/* Using CALL SYMPUT */
DATA _NULL_;
    SET work.summary;
    WHERE region = 'West';
    CALL SYMPUT('west_total', total_sales);
RUN;

%PUT Total West Sales: &west_total;

2.2.2 Create and Use Macros

• Define macros with %MACRO and %MEND
• Use macro parameters
• Implement conditional logic in macros

/* Example: Creating macros */
%MACRO generate_report(dataset=, year=, output=);
    PROC MEANS DATA=&dataset NOPRINT;
        WHERE year = &year;
        VAR sales revenue;
        OUTPUT OUT=&output
               SUM(sales)=total_sales
               SUM(revenue)=total_revenue;
    RUN;
    
    PROC PRINT DATA=&output;
        TITLE "Summary Report for Year &year";
    RUN;
%MEND generate_report;

/* Calling the macro */
%generate_report(dataset=work.sales_data, 
                 year=2024, 
                 output=work.summary_2024);

2.2.3 Macro Functions

• %EVAL and %SYSEVALF
• %SUBSTR, %SCAN, %UPCASE
• %STR and %NRSTR

/* Example: Macro functions */
%MACRO process_data(start_year=, end_year=);
    %DO year = &start_year %TO &end_year;
        PROC PRINT DATA=work.sales_&year;
            TITLE "Sales for Year &year";
        RUN;
    %END;
%MEND process_data;

%process_data(start_year=2020, end_year=2024);

2.3 3. Advanced DATA Step Programming (20%)

2.3.1 Array Processing

• Create and use one-dimensional arrays
• Create and use multi-dimensional arrays
• Process array elements

/* Example: Array processing */
DATA work.quarterly_analysis;
    SET work.sales;
    
    ARRAY quarters{4} q1 q2 q3 q4;
    ARRAY pct_change{3} pct_q2 pct_q3 pct_q4;
    
    /* Calculate percentage changes */
    DO i = 2 TO 4;
        pct_change{i-1} = (quarters{i} - quarters{i-1}) / quarters{i-1} * 100;
    END;
    
    /* Calculate annual total */
    annual_total = SUM(OF quarters{*});
    
    DROP i;
RUN;

2.3.2 Hash Objects

• Create and use hash objects
• Implement efficient lookups
• Use hash iterators

/* Example: Hash object for lookup */
DATA work.enriched_data;
    IF _N_ = 1 THEN DO;
        DECLARE HASH lookup(DATASET: "work.reference_data");
        lookup.DEFINEKEY('id');
        lookup.DEFINEDATA('category', 'description');
        lookup.DEFINEDONE();
    END;
    
    SET work.main_data;
    
    rc = lookup.FIND();
    IF rc = 0 THEN output;
    
    DROP rc;
RUN;

2.3.3 DO Loop Processing

• Iterative DO loops
• DO WHILE and DO UNTIL loops
• Nested loops

/* Example: Advanced DO loops */
DATA work.compound_interest;
    principal = 10000;
    annual_rate = 0.05;
    
    DO year = 1 TO 10;
        interest = principal * annual_rate;
        principal = principal + interest;
        OUTPUT;
    END;
RUN;

/* DO UNTIL example */
DATA work.convergence;
    x = 1;
    iteration = 0;
    
    DO UNTIL (ABS(x - x_prev) < 0.001 OR iteration > 100);
        x_prev = x;
        x = x / 2 + 1 / x;
        iteration + 1;
        OUTPUT;
    END;
RUN;

2.4 4. Advanced Techniques (20%)

2.4.1 Using PROC FCMP

• Create custom functions
• Use custom functions in DATA steps

/* Example: Creating custom functions */
PROC FCMP OUTLIB=work.funcs.math;
    FUNCTION calculate_discount(amount, rate);
        discount = amount * rate;
        RETURN(discount);
    ENDSUB;
QUIT;

OPTIONS CMPLIB=work.funcs;

DATA work.with_discounts;
    SET work.sales;
    discount = calculate_discount(price, 0.10);
    final_price = price - discount;
RUN;

2.4.2 Using Indexes

• Create simple and composite indexes
• Understand when to use indexes
• Use WHERE vs. subsetting IF

/* Example: Creating and using indexes */
PROC DATASETS LIBRARY=work NOLIST;
    MODIFY sales;
    INDEX CREATE customer_id;
    INDEX CREATE region_product = (region product);
QUIT;

/* Indexes are automatically used with WHERE statements */
DATA work.subset;
    SET work.sales;
    WHERE customer_id = '12345';  /* Uses index */
RUN;

2.4.3 Combining Data Vertically and Horizontally

• Advanced merging techniques
• Update and modify operations
• Interleaving data sets

/* Example: UPDATE statement */
DATA work.master;
    UPDATE work.current_data work.changes;
    BY id;
RUN;

/* Example: Interleaving */
DATA work.combined;
    SET work.data2020 work.data2021 work.data2022;
    BY date;
RUN;

3 Study Resources

3.1 Official SAS Resources

1. SAS Programming 2: Data Manipulation Techniques - Official SAS training
2. SAS Programming 3: Advanced Techniques and Efficiencies - Official SAS training
3. SAS Macro Language 1: Essentials - Official SAS training
4. SAS SQL 1: Essentials - Official SAS training

3.2 Practice Tips

1. Master SQL
   • SQL accounts for 35% of the exam
   • Practice complex joins and subqueries
   • Understand the differences between SQL and DATA step
2. Understand Macros
   • Practice writing parameterized macros
   • Learn macro debugging techniques
   • Understand macro quoting functions
3. Advanced DATA Step
   • Master array processing
   • Learn hash object techniques
   • Practice complex DO loops
4. Performance Optimization
   • Understand when to use indexes
   • Learn efficient data processing techniques
   • Compare SQL vs. DATA step performance

4 Common Pitfalls

1. Macro quoting issues - Use %STR, %NRSTR, %BQUOTE correctly
2. Hash object syntax - Remember to declare and define before using
3. SQL vs. DATA step confusion - Understand when to use each approach
4. Scope issues with macro variables - Understand local vs. global scope

5 Sample Questions

5.1 Question 1

What is the purpose of the %NRSTR function?

Answer: %NRSTR prevents macro resolution and removes the meaning of special characters and mnemonic operators during compilation, including the ampersand (&) and percent sign (%).

5.2 Question 2

Which join type returns all rows from both tables?

A. Inner Join B. Left Join C. Right Join D. Full Outer Join

Answer: D - Full Outer Join returns all rows from both tables, with NULL values where there’s no match.

6 Next Steps

After completing Advanced Programming certification:

1. Apply advanced techniques in real-world scenarios
2. Consider specialized certifications (Clinical Trials, Data Mining, etc.)
3. Contribute to the SAS community

---

← Back to Home