SAS Programming Advance Certification Course(SAS SQL, Macro)

Learn to upload data files and SAS programs to SAS OnDemand for Academics by extracting zip archives, organizing into a SAS base codes folder, and uploading to the home drive.

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 create and reference new columns in proc sql, using text output, labels, and calculated aliases for output, where, and order by.

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 summarize data with proc sql by applying aggregate functions, computing mean, min, max, and sum, using where and order by, and counting distinct, non-missing, and all rows.

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.

Explore left, right, and full outer joins in SAS proc SQL by combining two data sets on store id, highlighting matching and nonmatching rows with the on clause.

Compare data step match merge with proc sql joins, showing results when all values match, and that you do not have to sort, using coalesce to overlay id and name.

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 correlated subqueries and how to combine a join with a subquery to compare each student's mean to their class average using SAS SQL.

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.

Learn to concatenate query results with the outer union operator in SAS SQL, and overlay same-position columns using the core keyword to align results.

Explore exclusive union in proc sql by producing rows that appear in either first or second query but not both, using first except second and second except first, then union.

Use the like clause to create an empty table with the same columns and attributes, and copy an existing table with a select all in proc sql.

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.

Use the update statement to modify all rows in a column with the same expression, demonstrated by creating a new score data table from an imported Excel file and updating score one by 10% where it is below 85.

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.

Create proc sql views to store queries as new score data, compute score av with min of score variables, and grade via a case expression; describe and drop the view.

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.

Compare old and new SAS tables with proc sql by importing Excel files into score data old and score data new, use accept to reveal updated and added rows.

Overlay missing data values using coalesce in SAS SQL by joining two tables and filling score and gender fields with non-missing values.

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.

Sorts data by a customized month order in SAS SQL using an inline view and a case expression to create a sorter column, then order by sorter and 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.

Pass information into a macro by defining macro parameters in the macro statement, creating local macro variables that execute only during the macro and prevent external interference.