==> Level Beginner > Introduction to SAS

Starting with a new software and programming language is not easy. This tips and tricks helps you to understand the basics in SAS and how to access your data , sample data and help files in order to start in the good direction.

This tips and tricks is designed for non IT persons who would like to start with the SAS programming language. We will talk about the SAS programming language and the SAS environments in order to demystify it:

• How can we access SAS?
• What is a SAS program and how can we execute it?
• What is the naming convention of the language?
• What is a SAS dataset?
• How can we access data in SAS?
• Use proc import/export
• What is a SAS format, where can I find the list of default SAS formats ?
• What is a SAS label, how can I use it in a procedure ?
• Introduction to the macro language in order to simplify the code.

You have no SAS experience and would like to understand the basic terminology of the SAS programming language and how to access data.

Reference id: 1
Course Category: Programming
Course Level: Beginner
Course Duration : 3 Hours

==> Level Beginner > SAS data step in a nutshell

The SAS data step is the most important step of a SAS program and corresponds to the step where the data are created. This tips and tricks guides you on the major statements of this step allowing you to create data in the best way.

In SAS, nearly all data manipulation can be performed. This flexibility is possible thanks to the data step, a piece of SAS code allowing you to create and manipulate data ! In this tips and tricks, we will see the main statements of this step allowing you to access, manipulate and create data:

• Create dataset with datalines,
• Read and create several datasets,
• Control the variables using the KEEP and DROP statements,
• Filter data using the where statement,
• Create new variable with the assignment statement,
• Create variable conditionally with the if then else logic and case when logic.
• Use functions in filters or in variable creation,

We will also see how we can add label, specify the type, the length and add format to the variables.

You have no experience in SAS and would like to be introduced to the data step programming language.

Reference id: 2
Course Category: Data Step
Course Level: Beginner
Course Duration : 6 Hours

==> Level Beginner > SAS Proc SQL

Structured Query Language (SQL) is a very known language used to query relational database. Since 1986, this langage is fully integrated in SAS and can be used via the Proc SQL .

Creating or accessing tables using SQL syntax have lot of advantages for Data Step programmer, this tips and tricks covers everything you have to know concerning the usage of the SQL into SAS. Table of contents:

• select variables and observations,
• add label and format to your report,
• create variables conditionally,
• Use binary expression to count records having specific pattern,
• subquery in where clause,
• aggregate values,
• filter aggregated values,
• create table and view,
• join tables,
• concatenate tables.

You have no SAS experience and would like to understand the basic terminology of the SAS proc SQL.

Reference id: 3
Course Category: SQL
Course Level: Beginner
Course Duration : 3 Hours

==> Level Beginner > Creating reports using Base SAS

Base SAS contains couple of very nice procedures to create reports on your data . This tips and tricks shows you an overview of the most popular including The Proc Tabulate which is one of the most powerful SAS reporting Procedure.

In this tips and tricks, we will do an end-to-end approach on the report creation using Base SAS, more precisely we will have a look to:

• Creating and using custom SAS format,
• Sort your data and remove duplicates using the PROC SORT,
• Introduction to basic reports: Proc Print, Proc means and Proc Freq ,
• Introduction to advanced reports: Proc Univariate, Proc Report and Proc Tabulate ,
• Combining results using ODS Layout
• Exporting reports in PDF, WORD, XLSX or HTML using ODS

You have some SAS experience and would like to use SAS procedures to create reports.

Reference id: 4
Course Category: Programming
Course Level: Beginner
Course Duration : 6 Hours

==> Level Intermediate > Demystify the SAS Program Data Vector

Reading flat files in SAS Data Step gives you a lot of flexibility in terms of data quality and data manipulation. However, problem may arise when files are huge with poor data quality. In this tips and tricks we will have a look to the 'heart' of the Data Step manipulation and learn to do as much as we can do in this important step: applying data quality rules, creating error datasets, selecting good variables and records (etc.)

The Program Data Vector (PDV) is the heart of the SAS Data Step processing. In this tips and tricks, you will learn what is the PDV and how you can take advantage of it in order to optimize your SAS Data Step code and understand your log better.
More precisely, we will answer the following questions:

What is the Program Data Vector?

• When is it created, where is it stored, how could I see it?
• Using Keep and Drop to select variables
• Using subsetting if and where statement to select records
• Understand when PDV variables are reset to missing
• Retaining variable values through Data Step iteration
• Understanding the default PDV variables _N_ and _ERROR_

How could I make sure the data loaded have good quality?

• Count number of records having bad pattern VS the one having good pattern
• Create a separate dataset to store bad data for further investigation
• Generate an Email or stop the current process when some event are triggered.

You are a SAS beginner or used SAS long time ago in the past and would like to refresh your skills.

Reference id: 6
Course Category: Data Step
Course Level: Intermediate
Course Duration : 3 Hours

==> Level Intermediate > Merging data with DataStep

In this tips and tricks we will have a look to the merge statement of the SAS DataStep in order to combine datasets.

Combining data is very common in data manipulation, this allows you to get information contained in a table and to combine them with information contained in an another table. Your data can be combined horizontally (join, merge) or vertically (set, append) and this can be done in datastep or proc sql. This tips and tricks is focused on the merge statement .
Table of contents:

• one to one merging
• inner join + non-matching rows
• one to many merging
• many to many merging
• update records with merge statement
• create accumulated variable with sum statement
• by group processing
• aggregation in datastep
• doing more with proc sort

You have some SAS experience and would like to join or append data sets in data step or SQL.

Reference id: 7
Course Category: Data Step
Course Level: Intermediate
Course Duration : 3 Hours

==> Level Intermediate > Mastering SAS functions

SAS has more than 190 of 'built-in' functions allowing you to perform a variety of programming tasks. It would be a burden to explain them all in an half day, that's the reason why we selected the most useful one in this tips and tricks.

All programming language have functions. SAS offers more than 190 functions in order to perform data manipulation and they can be used for variable creation, filtering, conditional processing, test the availability of something... In this tips and tricks, we will first review the main elements of a function, where to use them and where can we find the list of 'built-in' functions in SAS. Then we will dive in and see the following function selection:

• filter data using find function
• extract characters using substr function
• extract words in a string with scan function
• concatenate strings with cat functions
• use variable lists
• other character functions
• date functions
• numeric functions
• regex functions
• create your functions with proc FCMP

You have some SAS experience and would like to use SAS functions in order to manipulate your data.

Reference id: 8
Course Category: Data Step
Course Level: Intermediate
Course Duration : 3 Hours

==> Level Intermediate > Create and use formats in SAS

SAS formats are instruction that SAS uses to write data values. You use formats to control the written appearance of data values, or, in some cases, to group data values together for analysis. You can create format manually with hardcoded values or from datasets, you can store them in permanent library, share them and also use build-in format (etc.) every thing you want to know about SAS format should be present in this tips and tricks.

SAS formats are useful instruction which can be used in a wide range of circumstances. In this tips and tricks, we will have a look on variety of aspects on the SAS formats:

• Create format with hardcoded values and use it on data step and procedure
• Create format on the basis of an input dataset
• Update format
• View format contents
• Create picture format to create patterns and to create date patterns
• Find the list of SAS default formats and informats on SASHELP
• Store the format in permanent or temporary library and view the options to select one rather than an another one
• Apply format in statistical procedure to change classification
• Validate data using format
• Use format to convert physically the content of the variable
• Create informat and use it on data step to create numeric variable.

You have some SAS experience and would like to validate your data against reference table using SAS format.

Reference id: 9
Course Category: Data Step
Course Level: Intermediate
Course Duration : 3 Hours

==> Level Intermediate > Using SAS arrays, do loops and variable lists

At the era of the big data, it's very common to have lot of variables to manipulate in blocks of variables. Arrays, Do loops and variable lists are powerful data manipulation tools that help make code more efficient for repetitive operation.

SAS arrays are Data Step objects allowing you to group and reference your variables. Once created, the variables of your array are called elements and can be dynamically referenced by their position in the array. This technic is particularly interesting if you have to modify the values of x variables in your dataset, in that case you just have to know the position of your variables in your array to process them. Do loop, allows you to execute statements repetitively. Combining SAS arrays with do loop allows you to treat all the elements dynamically, that is the reason why both topics are often discussed together.
In this tips and tricks we will first talk about the SAS arrays then we will talk about do loop and finally we will combine them !:

• Creation of SAS array
• Manipulate SAS array with variable lists and functions
• Creating a basic do loop
• Creating do while and do until loop
• Creating do loop to process all the elements of a list

Then, we will illustrate the theory with several practical examples

• replace by 0 all the missing numeric variables
• Comparing values of a dataset with initial values
• Convert all characters to upcase
• Create multidimentional array
• Rotate dataset from wide to narrow
• Rotate dataset from narrow to wide

You have some SAS experience and want to use SAS arrays and do loops in order to speed up your code development.

Reference id: 10
Course Category: Data Step
Course Level: Intermediate
Course Duration : 3 Hours

==> Level Intermediate > SAS macro variables

Macro variables are tools that enable you to dynamically modify the text in a SAS program through symbolic substitution. You can assign large or small amounts of text to macro variables, when you reference the variable afterward the text that it contains will substitute the variable name.

In this tips and tricks, we will start the SAS Macro language from scratch and learn how to create and use macro variables in order to dynamize SAS codes. More precisely, we will cover the following topics:

• How can we create macro with %let statement ?
• What are the system macro variables and how to use them
• Use macro together with SAS code
• Use character macro functions
• How to deal with numeric values using macro functions
• Creation of the macro using the CALL SYMPUTX in datastep
• Creation of the macro using the INTO clause in PROC SQL

You have some SAS experience and would like to use and create SAS macro variables in your SAS codes.

Reference id: 11
Course Category: Macro
Course Level: Intermediate
Course Duration : 6 Hours

==> Level Intermediate > SAS macro programs

Macro programs or Macros are compiled line of codes referred by a name that you can call anywhere in a SAS program using that name. This allows you to create SAS toolbox of code which can be used in different codes, using different parameters, using Conditional processing....

In this tips and tricks, we will cover the very interesting topic of the macro programs:

• What are macro program and how can we create and use them ?
• What kind of parameters exists and how to we use them?
• How can we use conditional expression
• How can we loop using do loop & do while
• How can we perform data driven execution of macro programs
• Difference between Global symbol table VS Local symbol table
• Macro program storage: Compiled in permanent library, autocall or autoexec ?

You have some SAS experience, already know what is a macro variable and would like to use and create SAS macro programs.

Reference id: 12
Course Category: Macro
Course Level: Intermediate
Course Duration : 6 Hours

==> Level Intermediate > Data Step VS Proc SQL: really different?

It's not always easy to choose between Proc SQL and Data Step programming. In this tips and tricks, we will see the big differences between Proc SQL and Data Step and list the pro's and the con's of both of them.

In SAS, you can choose between Proc SQL and Data Step when you do data manipulation. Do the two methods generate the same results? Are they both efficient in terms of data processing or ease of use? When should I use one technic or the other technic?
In this tips and tricks, we will compare both methods on the basis of the following:

• Read one input and create one or several output
• Read several input and create one output or several
• Code complex algorithms implying lot of variables
• Aggregate results
• Create simple or complex report
• Read non SAS files (flat or database)

You have some SAS experience and would like to create compare Proc SQL and SAS Data step

Reference id: 13
Course Category: SQL
Course Level: Intermediate
Course Duration : 3 Hours

==> Level Intermediate > Creating Stored processes

SAS Stored processes (SAS V9) can be used to generate dynamic reports from web browser, SAS Add in of MS Office, SAS VA (etc.) It's not always easy to understand the capabilities of the different options proposed in the parameters wizard creation nor to identify how you can retrieve the values of the parameters within your SAS code. This tips and tricks helps you to put some light on that.

In this tips and tricks, we will first talk about the SAS Stored processes:

• What is a SAS STP ?
• How and where can we use them?
• How can we create them using the SAS Management Console or SAS Enterprise Guide?
• What is the meaning of the different options of the wizard?
• What is a SAS parameter?
• How can we create parameters?

Then, we will go one step further and see how we can

• The different types of parameters of the SAS prompting framework
• Generate default values and dynamic or static list of values
• Create group of prompts
• Create cascading prompts
• Reuse the parameter values in SAS code

You have some SAS experience and have already created SAS reports.

Reference id: 27
Course Category: STP
Course Level: Intermediate
Course Duration : 3 Hours

==> Level Intermediate > SAS Viya for SAS users

SAS Viya is the latest enhancement of the SAS Platform. When you are SAS9 programmer you can continue using your SAS9 syntax in SAS Viya. However, if you want to take advantage of capabilities such as parallel processing or in memory processing offered by SAS Viya, you need to know a little bit more than SAS9 syntax.
This tips and tricks is designed for people who already know SAS9 and want to learn the basics of SAS programming in SAS Viya.

In this tips and tricks, we will first talk about the differences between SAS9 and SAS Viya. Then we will dive into the main statements and procs so that you will be able to work easily in this new environment. More precisely, we will see how to :

• Create CAS Session
• Create SAS 9 library and CAS library
• Load/unload data in Memory
• Save in memory data
• Use DATA Step in CAS
• Use PROC FEDSQL in CAS
• Other SAS procedures supported by CAS

You have some SAS experience in SAS 9 and are familiar with Data Steps and PROC SQL.

Reference id: 33
Course Category: SAS Viya
Course Level: Intermediate
Course Duration : 3 Hours

==> Level Intermediate > Run DataStep in CAS for SAS users

SAS Viya is the latest enhancement of the SAS Platform. When you are SAS9 programmer you can continue using your SAS9 syntax in SAS Viya. However, if you want to take advantage of capabilities such as parallel processing or in memory processing offered by SAS Viya, you need to know a little bit more than SAS9 syntax.
This tips and tricks is designed for people who already know Data Step programming in SAS9 and want to learn what you can and cannot do in SAS Data Step programming executed by the CAS server .

In this tips and tricks, we will first make a summary of the most important things discussed in the introduction SAS Viya for SAS users tips and tricks. Then we will dive into the main Data Step topics which differs from SAS9 when you are working on the CAS server . More precisely, we will see :

• How to load, run and promote tables via DATASTEP
• the multi-threading problematic
• the by-group processing
• How to create and apply formats in CAS
• Data Steps statements or options not supported in CAS

This tips and tricks is also interesting when you start with SAS programming with SAS Viya or if you plan to do a migration from SAS9 to SAS Viya.

You have some SAS experience in SAS 9 and are familiar with Data Steps. Moreover, you already followed the introduction SAS Viya for SAS users tips and tricks.

Reference id: 34
Course Category: SAS Viya
Course Level: Intermediate
Course Duration : 3 Hours

==> Level Intermediate > Run PROC FEDSQL in CAS for SAS users

SAS Viya is the latest enhancement of the SAS Platform. When you are SAS9 programmer you can continue using your SAS9 syntax in SAS Viya. However, if you want to take advantage of capabilities such as parallel processing or in memory processing offered by SAS Viya, you need to know a little bit more than SAS9 syntax.
PROC FEDSQL can be executed in CAS and the syntax looks like the PROC SQL syntax. However, it is totally incorrect to say that you could copy paste Proc SQL statements/clauses into Proc FEDSQL. This tips and tricks is designed for people who already know PROC SQL and think that PROC FEDSQL is the replacement of PROC SQL.

When I started with Proc FEDSQL in CAS, I was convinced that this proc was a new 'super' Proc SQL running only on CAS server. Well it is not really the case but this proc has some advantages. In this tips and tricks we will first make a summary of the most important things discussed in the introduction SAS Viya for SAS users tips and tricks. Then we will dive into the PROC FEDSQL in CAS with topics such as:

• Use PROC SQL to load data to CAS
• PROC FEDSQL is not specific to CAS
• Run FEDSQL in CAS
• Why should I use proc FEDSQL.
• Use FEDSQL to query ANSI SQL3 tables
• Use FEDSQL to combine tables of different source

Then we will discuss couple of differences compared with PROC SQL:

• SESSREF, memnemonic and libname
• NO REMERGE, NO CALCULATED, NO NUMBER
• Add format and label
• Create macro with into clause
• Use insert into

This tips and tricks is also interesting when you start with SAS programming with SAS Viya or if you plan to do a migration from SAS9 to SAS Viya.

You have some SAS experience in SAS 9 and are familiar with PROC SQL. Moreover, you already followed the introduction SAS Viya for SAS users tips and tricks.

Reference id: 35
Course Category: SAS Viya
Course Level: Intermediate
Course Duration : 3 Hours

==> Level Intermediate > Access CAS for Python users

SAS Viya is the latest enhancement of the SAS Platform. Since this release you can, as a Python programmer, access data and procedures available on the SAS Viya platform.
This can be done by inserting Python Nodes in your data flows but this can also be done thanks to the SWAT python package (SAS Scripting Wrapper for Analytics Transfer).
What does that mean? You can do with Python everything you can do in SAS: load/unload data in memory, doing parallel processing, accessing databases data via SAS libraries, working in a secured environment, use remote server. etc.
This tips and tricks is designed for people who know already Python, SQL, Pandas and who would like to access SAS Viya from Python.

In this tips and tricks, we will first make a summary of the most important things discussed in the introduction SAS Viya for SAS users tips and tricks. Then we will dive into SWAT package in order to :

• Make connection and access CASLIB
• List objects, load & drop tables in CASLIB
• Save data into SASHDAT
• Load data to CASLIB
• Manipulate data in SAS and get the results in Python and VICE VERSA
• Query the data with FEDSQL
• Query the data with data step
• Display data with CASL
• Query CAS table like in Pandas with CAS table object
• Push CAS table to DATAFRAME

What I love in that tips and tricks is that you can really do data manipulation in Python and then push your data to SAS in order to visualize them in SAS Visual Analytics or other analytics modules. Moreover, thanks to CAS server, it is super easy to manipulate huge datasets in memory and parallel processing.

You have some Python experience and would like to access SAS Viya from Python.

Reference id: 36
Course Category: SAS Viya
Course Level: Intermediate
Course Duration : 3 Hours

==> Level Intermediate > SAS macro variables for SQL users

Macro variables are tools that enable you to dynamically modify the text in a SAS program through symbolic substitution. You can assign large or small amounts of text to macro variables, when you reference the variable afterward the text that it contains will substitute the variable name.

In this tips and tricks designed especially for SQL users, we will start the SAS Macro language from scratch and learn how to create and use macro variables in order to dynamize SAS codes. More precisely, we will cover the following topics:

• How can we create macro with %let statement ?
• What are the system macro variables and how to use them
• Use macro together with SAS code
• Use character macro functions
• How to deal with numeric values using macro functions
• Creation of the macro using the INTO clause in PROC SQL

You have some SAS experience and would like to use and create SAS macro variables in your SAS codes.

Reference id: 37
Course Category: Macro
Course Level: Intermediate
Course Duration : 6 Hours

==> Level Intermediate > SAS macro programs for SQL users

Macro programs or Macros are compiled line of codes referred by a name that you can call anywhere in a SAS program using that name. This allows you to create SAS toolbox of code which can be used in different codes, using different parameters, using Conditional processing....

In this tips and tricks designed especially for SQL users, we will cover the very interesting topic of the macro programs:

• What are macro program and how can we create and use them ?
• What kind of parameters exists and how to we use them?
• How can we use conditional expression
• How can we loop using do loop & do while
• How can we perform data driven execution of macro programs
• Difference between Global symbol table VS Local symbol table
• Macro program storage: Compiled in permanent library, autocall or autoexec ?

You have some SAS experience, already know what is a macro variable and would like to use and create SAS macro programs.

Reference id: 38
Course Category: Macro
Course Level: Intermediate
Course Duration : 6 Hours

==> Level Expert > Create and use indexes in SAS

Indexes are special reference tables that the SAS engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. In this tips and tricks we will discuss how you can create and use SAS indexes.

Queries created couple of years ago could be more slow today just because the size of the tables queried increased, those tables could require an index?
Before jumping in the detail of the SAS Indexes, an overview of it is way of working will be described:

• Where are stored the indexes and what do they contains?
• Once created, are they used by default?
• What are the different kind of indexes?

We will then see how we can create or delete them using Proc SQL or Data step or Proc Datasets:

• Create simple index
• Create composite index
• Create unique index

Finally, we will see different options allowing you to use them. Via proc sql, it is quite straightforward but with datastep it is not that obvious and this will force us to review the different types of data access: sequential, direct (POINT and MODIFY) or direct via index (KEY and MODIFY). Part of this tips and tricks will be spent on match merge technics in datastep using the _IORC_, _SOK and _SDENOM.

You have some SAS experience and would like to create and use indexes in order to speed up your queries on big data.

Reference id: 14
Course Category: Programming
Course Level: Expert
Course Duration : 3 Hours

==> Level Expert > Create and use hash objects in SAS

The SAS hash object is meant to enable you to quickly and efficiently store, search, and retrieve data based on lookup keys , Conceptually, the hash object provides programmers the means to easily define and utilize a hash table within the Data Step.

SAS developed different in-memory technics to perform lookup. In this tips and tricks, we will will dive into Hash Object:

• What is a Hash Object?
• What is the syntax of an Hash Object?
• How can we load values in the Hash Object? (manually or from dataset)
• How can we deal with duplicates ?
• How can we add more keys to improve unicity?
• How can we combine different hash objects in a single datastep

In a second step, we will apply the syntax of the hash object during the read phase of a CSV. The idea behind will be to validate the data (compared to a reference table loaded in memory) and to output records to an error table if they do not meet acceptance criteria.

You have some SAS experience and would like to create and use hash objects in order to speed up your lookup.

Reference id: 20
Course Category: Data Step
Course Level: Expert
Course Duration : 3 Hours

==> Level Expert > Read and create flat files with Data Step

Creating or reading flat files from SAS can be done in several ways in SAS: proc import, proc export are common examples. When flat files are not simple line of records, it becomes impossible to use default technics and Data Step programming becomes required.

How many keywords do we have to change in a datastep in order to write a flat file as the place of reading it? only two !!
In this tips and tricks, we will mainly have a look to the different technics allowing you to read flat files using datastep because writing flat files is the same:

• List input,
• Named Input ,
• Column Input ,
• Formatted Input ,
• Mixed Input ,
• Read interleaved flat file,
• Hold input buffer using @ and @@,
• and finally, generate flat files with header and footer.

You have some SAS experience and would like to use the flexibility of the Data Step to read and create flat files.

Reference id: 21
Course Category: Data Step
Course Level: Expert
Course Duration : 3 Hours

==> Level Expert > Create Slowly Changing Dimension (SDC1 and 2) in Data Step

A Slowly Changing Dimension (SCD) is a dimension that stores and manages both current and historical data over time in a data warehouse. It is considered and implemented as one of the most critical ETL tasks in tracking the history of dimension records. Different SCD strategies could be implemented and this tips and tricks is focused on SCD1 where records are updated no history is kept and SCD2 where history is kept,

To ease the concept, in this tips and tricks we will use the table SASHELP.CLASS and considering name & sex as SCD1 columns and age, height & weight as SCD2 columns.
We will first analyse the SAS code block by block and then test it to crosscheck that everything is working as expected. The following SAS technics will be discussed::

• using proc sort to sort and remove duplicate entries
• using merge statement to combine dimension and landing data
• compare records values using md5 function
• create tables simultenaously using explicit output statement
• update table using modify/replace statements
• load new records using proc append

You have some SAS experience and have knowledge in Datawarehouse. You want to update existing tables using SCD method.

Reference id: 24
Course Category: Programming
Course Level: Expert
Course Duration : 6 Hours