This SAS code uses the SQL procedure (`PROC SQL`) to...

This SAS code uses the SQL procedure (PROC SQL) to create a new dataset (cnt_ds) by performing a LEFT JOIN operation between two existing datasets (survcnt_ds as a and defcnt_ds as b). Below is an explanation of what the code does step-by-step:

1. create table cnt_ds as: This line specifies that the result of the SQL query will be saved as a new dataset named cnt_ds.

2. select clause: This specifies the columns to include in the resulting dataset:

   • "current_quarter"d as current_quarter format=date9.,: The value "current_quarter"d is selected, assigned the column name current_quarter, and formatted using DATE9. format (e.g., 01JAN2023).
   • next_quarter_num as next_quarter format=date9.,: The column next_quarter_num is selected from the input datasets, renamed as next_quarter, and formatted using the DATE9. format.
   • coalesce(a.distinct_entity_count,0) as survived_entity_cnt,: The column distinct_entity_count from survcnt_ds (a) is selected. The COALESCE function ensures that if this column contains a NULL value, 0 is used instead. The result is renamed as survived_entity_cnt.
   • coalesce(b.distinct_entity_count,0) as default_entity_cnt: Similarly, the column distinct_entity_count from defcnt_ds (b) is selected. The COALESCE function ensures that any NULL value is replaced with 0, and the result is renamed as default_entity_cnt.

3. from survcnt_ds as a: Specifies the first input dataset, survcnt_ds, and gives it the alias a.

4. left join defcnt_ds as b on 1=1: Specifies a LEFT JOIN between survcnt_ds (a) and defcnt_ds (b). However, the condition on 1=1 means there is no specific joining condition, resulting in a Cartesian product of the two datasets where all rows in a are joined with all rows in b, limited by the structure of a LEFT JOIN.

5. quit;: Ends the SQL query.

Purpose:

The code creates a new dataset (cnt_ds) that combines columns and values from survcnt_ds and defcnt_ds. Specifically, it calculates two measures:

• survived_entity_cnt: The count of surviving entities from survcnt_ds (defaults to 0 if no data exists).
• default_entity_cnt: The count of defaulted entities from defcnt_ds (defaults to 0 if no data exists).

The output will contain all rows from survcnt_ds and corresponding aggregate values from defcnt_ds.