SAS Programming Advance Certification Course(SAS SQL, Macro)

update sas source programs for sas on demand for academics by changing the home folder path to the correct directory, save the updated files, and validate with a run log.

Explore proc sql as a base SAS tool for SAS data sets, using stored select statements and queries to retrieve data with where clauses, comparing to the data step.

Learn to use the select clause in proc sql to choose all or specific columns, and apply the distinct keyword to eliminate duplicate rows and describe table attributes.

Learn to conditionally assign values in proc SQL with a simple case expression and the case operand form, creating grade and score_av variables from score data.

Learn how to replace missing values in SAS proc SQL using the coalesce function and a case expression, updating the original score one and gender variables with concise, comparable results.

Learn how to use the order by clause in proc sql to sort data, control ascending or descending order, handle nulls, and sort by multiple columns or calculated aliases.

Learn to retrieve rows that satisfy a condition with the where clause in proc sql, using comparison, logical, in, is missing, between, like, and truncated string operators, handling missing values.

Learn to group data with the proc sql group by clause, apply aggregate functions (mean, max, min) by gender and class, and handle missing values.

Explore joining data from multiple tables using inner joins, via where clause or inner join with on, qualify column names to avoid ambiguity, and return only matching rows by store_id.

Show how null values affect joins in proc sql, causing unintended matches; learn to filter non-missing values and apply multi-column joins using store id and student name for accuracy.

Learn how to perform inner joins across more than two tables and self-joins in SAS sql, including importing data, aliasing tables, and handling missing values.

Learn to test for the existence of a group of values using exists and not exists in proc SQL, comparing subqueries that return rows versus empty results.

Explore multi-level subquery nesting in SAS SQL, from innermost to outer queries, using the min function to calculate student level mean for three score variables and compare with class averages.

Explore combining two SAS SQL queries with set operators, such as union and union all, to produce unique or all rows while aligning columns by position.

Use the except operator to return rows from the first query not in the second, and use except all to keep duplicates.

Learn how the intersect operator returns rows common to two queries, using mass and reed tables, with intersect and intersect all producing identical results for matching records like Sharon's 90.

Discover how to insert rows into SAS proc SQL tables using the set and values clauses, create an empty table with like, and insert two example rows with missing values.

Learn to insert rows with the values clause in SAS SQL, inserting by position and handling missing values: numeric with periods and character as spaces in single quotes.

Explore updating table rows with different expressions using update statements and a case expression in SAS SQL, demonstrating selective updates with where clauses and before-after comparisons.

Learn to delete rows in proc sql using the delete statement and a where clause to remove records, such as gender equals f, producing a filtered new score data.

Learn how to alter a table by adding a new column with the alter table add clause, including name, type, label, and format, then fill it using mean of scores.

Learn to alter table columns with proc sql: modify birth date label and format without changing data type, and drop a column to remove score3, validating updated score data.

Explore how to use sql procedure tables in SAS, create and insert into new tables, compute means for score variables, and drop tables to manage SAS data files.

learn to create and use proc sql views in SAS, treating views as input for data steps. the lecture demonstrates using a view with proc means to compute score means.

Learn to specify and use inline views in Proc SQL, placing a query in the from clause to create a temporary table of class means and student-level scores for filtering.

Compute percentages within subtotals for each class (A, B) showing yes or no wait in line, using proc sql with subqueries and joins on student name.

Show how to expand hierarchical data in a SAS SQL table using a self join to list each teacher with their supervisor, including teacher and supervisor IDs and names.

Learn to create a summary report with proc SQL using an inline view and case expressions to map rewards points by month (January to March), then sum by student name.

Update the score data table with values from the score one data table using proc sql, demonstrating create, update, and print steps.

Use macro variables to replace text strings in SAS code, referencing them with ampersands. Compute means for score one, score two, and score three via proc means.

Learn how to generate SAS code with macros by defining named macros, invoking them with a percent sign, and embedding constant text to build titles and procedures like proc print.