SAS - Only Important Parts

Chitranshu Vashishth

29th January 2025

Table of Contents

Create Datasets and set statement.

Here's a comprehensive tutorial on creating datasets and using the SET statement in SAS:

1. Creating Datasets in SAS

Basic Explanation:

In SAS, datasets (also called SAS tables) are the primary way to store and manipulate data. There are multiple ways to create datasets:

• Using DATA step

• Using PROC SQL

• Importing from external sources

Key Methods:

1. Basic Dataset Creation

DATA new_dataset;
    input name $ age salary;
    datalines;
John 30 50000
Mary 25 45000
Steve 35 60000
;
run;

2. Creating Dataset from Existing Dataset

xxxxxxxxxx
DATA new_dataset;
    SET existing_dataset;
run;

3. Creating Empty Dataset

​x
/* Method 1: Using NULL dataset */
DATA empty_dataset;
    SET sashelp.class(obs=0);
run;

/* Method 2: Using STOP statement */
DATA empty_dataset;
    stop;
run;

/* Method 3: With defined structure */
DATA empty_dataset;
    length name $50 age 8 salary 8;
    stop;
run;

2. SET Statement

Basic Explanation:

The SET statement is used to read observations from one or more existing SAS datasets.

Key Uses:

1. Reading Single Dataset

xxxxxxxxxx
DATA work.new_data;
    SET library.original_data;
run;

2. Combining Multiple Datasets (Vertically)

xxxxxxxxxx
DATA combined_data;
    SET dataset1 dataset2 dataset3;
run;

3. Using SET with Options

xxxxxxxxxx
DATA filtered_data;
    SET original_data(keep=account_id balance status);
    WHERE balance > 1000;
run;

Practical Example for Credit Risk Context:

xxxxxxxxxx
/* Creating a customer credit dataset */
DATA work.customer_credit;
    input customer_id name $ credit_score payment_status $;
    datalines;
1001 John 720 Good
1002 Mary 680 Fair
1003 Steve 800 Excellent
;
run;

/* Creating an empty template for bad loans */
DATA work.bad_loans;
    length customer_id 8 
           name $50 
           credit_score 8 
           payment_status $10 
           default_date 8;
    format default_date date9.;
    stop;
run;

/* Combining customer data with additional filters */
DATA work.high_risk_customers;
    SET work.customer_credit;
    WHERE credit_score < 700;
    risk_flag = 'High Risk';
run;

Common Pitfalls to Avoid:

1. Not specifying variable lengths in empty datasets

2. Forgetting to use RUN statements

3. Not considering dataset options (like KEEP, DROP, WHERE) when using SET

4. Not handling missing values appropriately

Best Practices:

1. Always define variable attributes (length, format) explicitly for empty datasets

2. Use meaningful dataset names

3. Comment your code

4. Consider using WHERE clauses for filtering during SET operations

5. Always verify the dataset creation with PROC CONTENTS or PROC PRINT

Keep columns when reading / appending / manipulating

KEEP Statement and Options

Basic Explanation:

KEEP is used to select specific variables (columns) to retain in the output dataset. It can be used in multiple ways:

1. As a dataset option

2. As a statement in DATA step

3. During dataset manipulation operations

Different Methods to Use KEEP

1. KEEP as Dataset Option

xxxxxxxxxx
/* Using KEEP as dataset option */
DATA new_dataset;
    SET original_dataset(KEEP=customer_id account_balance credit_score);
run;

2. KEEP as Statement

xxxxxxxxxx
/* Using KEEP as statement */
DATA new_dataset;
    SET original_dataset;
    KEEP customer_id account_balance credit_score;
run;

3. KEEP in Multiple Dataset Scenarios

xxxxxxxxxx
/* When combining datasets */
DATA combined_data;
    SET dataset1(KEEP=id name balance)
        dataset2(KEEP=id name balance);
run;

Practical Example for Credit Risk Context:

xxxxxxxxxx
/* First, create sample credit data */
DATA credit_master;
    input customer_id name $ age address $ 
          credit_score income account_balance 
          payment_history $ phone $;
    datalines;
1001 John 30 NYC 750 60000 5000 Good 555-0101
1002 Mary 25 LA 680 45000 2000 Fair 555-0102
1003 Steve 35 CHI 800 80000 10000 Excellent 555-0103
;
run;

/* Example 1: Keep essential credit risk variables */
DATA credit_risk_analysis;
    SET credit_master(KEEP=customer_id credit_score 
                          income account_balance 
                          payment_history);
run;

/* Example 2: Multiple datasets with different KEEP options */
DATA demographics credit_info;
    SET credit_master;
    /* Demographic information */
    IF _N_=1 THEN DO;
        KEEP customer_id name age address;
        OUTPUT demographics;
    END;
    /* Credit information */
    IF _N_=1 THEN DO;
        KEEP customer_id credit_score income payment_history;
        OUTPUT credit_info;
    END;
run;

/* Example 3: Combining with WHERE clause */
DATA high_risk_customers;
    SET credit_master(KEEP=customer_id credit_score 
                          payment_history income);
    WHERE credit_score < 700 AND income < 50000;
run;

Advanced Usage with MERGE:

xxxxxxxxxx
/* Using KEEP with MERGE statement */
DATA combined_risk_profile;
    MERGE customer_base(KEEP=customer_id name credit_score)
          transaction_history(KEEP=customer_id total_spend frequency);
    BY customer_id;
run;

Important Considerations:

1. Performance Impact:

• Using KEEP as a dataset option is more efficient than using it as a statement

• It reduces I/O by reading only necessary variables

2. Timing Differences:

xxxxxxxxxx
/* These two are not equivalent */

/* Option 1: Variables dropped before processing */
DATA new_data;
    SET old_data(KEEP=var1 var2);
    var3 = var1 + var2;
run;

/* Option 2: Variables dropped after processing */
DATA new_data;
    SET old_data;
    var3 = var1 + var2;
    KEEP var1 var2;
run;

Best Practices:

1. Documentation

xxxxxxxxxx
/* Always document which variables are being kept and why */
DATA risk_metrics /* Contains core risk variables */;
    SET full_customer_data(KEEP=
        customer_id    /* Primary key */
        credit_score   /* Risk indicator */
        payment_status /* Current status */
    );
run;

2. Variable Lists

xxxxxxxxxx
/* Using variable lists with KEEP */
DATA analysis_ready;
    SET raw_data(KEEP=
        customer_id 
        name 
        credit_: /* Keeps all variables starting with 'credit_' */
        risk_: /* Keeps all variables starting with 'risk_' */
    );
run;

Common Pitfalls to Avoid:

1. Not checking if required variables exist before using KEEP

2. Forgetting that KEEP options are processed before statements

3. Not considering the order of operations when using multiple data steps

4. Dropping variables needed for calculations before they're used

Quick Reference Table:

Drop columns when reading / appending / manipulating

DROP Statement and Options

Basic Explanation:

DROP is the opposite of KEEP - it's used to remove specific variables (columns) from the output dataset. Like KEEP, it can be used in multiple ways:

1. As a dataset option

2. As a statement in DATA step

3. During dataset manipulation operations

Different Methods to Use DROP

1. DROP as Dataset Option

xxxxxxxxxx
/* Using DROP as dataset option */
DATA new_dataset;
    SET original_dataset(DROP=phone_number address zip_code);
run;

2. DROP as Statement

xxxxxxxxxx
/* Using DROP as statement */
DATA new_dataset;
    SET original_dataset;
    DROP phone_number address zip_code;
run;

3. DROP in Multiple Dataset Scenarios

xxxxxxxxxx
/* When combining datasets */
DATA combined_data;
    SET dataset1(DROP=unnecessary_var1)
        dataset2(DROP=unnecessary_var2);
run;

Practical Example for Credit Risk Context:

xxxxxxxxxx
/* First, create sample credit data */
DATA credit_master;
    input customer_id name $ age address $ 
          credit_score income account_balance 
          payment_history $ phone $ email $ 
          marketing_flag $ last_contact_date $;
    datalines;
1001 John 30 NYC 750 60000 5000 Good 555-0101 john@email.com Y 01JAN2024
1002 Mary 25 LA 680 45000 2000 Fair 555-0102 mary@email.com N 15JAN2024
1003 Steve 35 CHI 800 80000 10000 Excellent 555-0103 steve@email.com Y 20JAN2024
;
run;

/* Example 1: Drop contact information for analysis */
DATA credit_analysis;
    SET credit_master(DROP=phone email marketing_flag 
                          last_contact_date);
run;

/* Example 2: Multiple datasets with different DROP options */
DATA risk_profile customer_contact;
    SET credit_master;
    /* Risk Profile - Drop contact info */
    IF _N_=1 THEN DO;
        DROP phone email address;
        OUTPUT risk_profile;
    END;
    /* Contact Info - Drop financial info */
    IF _N_=1 THEN DO;
        DROP credit_score income account_balance;
        OUTPUT customer_contact;
    END;
run;

/* Example 3: Combining with WHERE clause */
DATA high_balance_customers;
    SET credit_master(DROP=marketing_flag last_contact_date);
    WHERE account_balance > 5000;
run;

Advanced Usage:

1. Using DROP with MERGE:

xxxxxxxxxx
/* Using DROP with MERGE statement */
DATA risk_report;
    MERGE customer_data(DROP=internal_id update_date)
          credit_history(DROP=process_date batch_id);
    BY customer_id;
run;

2. Using DROP with Variable Lists:

xxxxxxxxxx
/* Dropping variables with common prefixes */
DATA clean_data;
    SET raw_data(DROP=temp_: /* Drops all variables starting with 'temp_' */
                     int_:  /* Drops all variables starting with 'int_' */);
run;

Important Considerations:

1. Performance Impact:

xxxxxxxxxx
/* More efficient - drops variables before reading */
DATA new_data;
    SET old_data(DROP=var1-var10);
run;

/* Less efficient - reads all variables first */
DATA new_data;
    SET old_data;
    DROP var1-var10;
run;

2. Timing Differences:

xxxxxxxxxx
/* These produce different results */

/* Option 1: Variables dropped before processing */
DATA new_data;
    SET old_data(DROP=var1);
    total = var1 + var2; /* This will fail - var1 already dropped */
run;

/* Option 2: Variables dropped after processing */
DATA new_data;
    SET old_data;
    total = var1 + var2;
    DROP var1;
run;

Best Practices:

1. Documentation

xxxxxxxxxx
/* Document dropped variables */
DATA risk_analysis /* Core risk analysis dataset */;
    SET full_data(DROP=
        temp_var1    /* Temporary calculation var */
        batch_id     /* Processing variable */
        update_flag  /* System flag */
    );
run;

2. Efficient Variable Selection

xxxxxxxxxx
/* Drop multiple related variables efficiently */
DATA clean_customer_data;
    SET raw_customer_data(DROP=
        _temp: /* Temporary variables */
        _flag: /* Flag variables */
        _sys_: /* System variables */
    );
run;

Common Pitfalls to Avoid:

1. Dropping variables needed for calculations

2. Not considering the order of operations

3. Dropping key identifier variables

4. Using DROP when KEEP might be more appropriate

Quick Reference Table:

Additional Tips:

1. Use PROC CONTENTS to verify dropped variables:

xxxxxxxxxx
PROC CONTENTS DATA=new_dataset;
run;

2. Combine with other options:

xxxxxxxxxx
DATA new_dataset;
    SET original_dataset(DROP=var1-var5 
                        WHERE=(balance > 1000)
                        FIRSTOBS=100);
run;

Data manipulation and creating new columns

Data Manipulation and Column Creation

Basic Explanation:

In SAS, you can create new columns through:

1. Direct assignment

2. Mathematical operations

3. Conditional logic

4. Functions

5. String operations

1. Basic Column Creation

xxxxxxxxxx
DATA new_data;
    SET original_data;
    
    /* Simple assignment */
    new_column = existing_column;
    
    /* Mathematical operation */
    total_balance = checking_balance + savings_balance;
    
    /* Percentage calculation */
    utilization_ratio = (current_balance / credit_limit) * 100;
run;

2. Practical Credit Risk Example

xxxxxxxxxx
/* Create sample credit data */
DATA credit_data;
    input customer_id 
          credit_score 
          income 
          debt_amount
          months_on_book
          current_balance;
    datalines;
1001 720 60000 15000 24 2000
1002 680 45000 20000 36 5000
1003 800 80000 10000 12 1000
;
run;

/* Comprehensive data manipulation example */
DATA credit_analysis;
    SET credit_data;
    
    /* 1. Basic calculations */
    debt_to_income = (debt_amount / income) * 100;
    monthly_income = income / 12;
    
    /* 2. Conditional logic using IF-THEN */
    IF credit_score >= 750 THEN credit_rating = 'Excellent';
    ELSE IF credit_score >= 700 THEN credit_rating = 'Good';
    ELSE IF credit_score >= 650 THEN credit_rating = 'Fair';
    ELSE credit_rating = 'Poor';
    
    /* 3. Using SELECT-WHEN */
    SELECT;
        WHEN (months_on_book >= 36) tenure_segment = 'Long-term';
        WHEN (months_on_book >= 12) tenure_segment = 'Medium-term';
        OTHERWISE tenure_segment = 'New-customer';
    END;
    
    /* 4. Creating flags */
    high_risk_flag = (credit_score < 650 AND debt_to_income > 40);
    
    /* 5. Using SAS functions */
    review_date = TODAY();
    months_since_review = INTCK('month', review_date, TODAY());
    
    /* 6. Numeric transformations */
    log_income = LOG(income);
    income_bracket = ROUND(income, 10000);
    
    /* 7. Creating categorical variables */
    LENGTH income_category $20;
    IF income < 30000 THEN income_category = 'Low';
    ELSE IF income < 70000 THEN income_category = 'Medium';
    ELSE income_category = 'High';
    
    /* 8. Complex calculations */
    risk_score = (credit_score * 0.4) + 
                 ((1 - debt_to_income/100) * 30) +
                 (months_on_book * 0.5);
run;

3. String Manipulation Examples

xxxxxxxxxx
DATA string_manipulations;
    SET customer_data;
    
    /* Converting case */
    upper_name = UPCASE(name);
    lower_name = LOWCASE(name);
    proper_name = PROPCASE(name);
    
    /* Extracting substrings */
    first_char = SUBSTR(name, 1, 1);
    
    /* Combining strings */
    full_address = CATX(' ', address, city, state, zip);
    
    /* Removing spaces */
    clean_id = COMPRESS(account_id);
run;

4. Date Manipulations

xxxxxxxxxx
DATA date_calculations;
    SET account_data;
    
    /* Create date variables */
    today = TODAY();
    current_time = TIME();
    
    /* Calculate durations */
    account_age_days = TODAY() - account_open_date;
    account_age_years = YRDIF(account_open_date, TODAY(), 'AGE');
    
    /* Format dates */
    FORMAT today account_open_date date9.
           current_time time8.
           account_age_years 5.2;
run;

5. Advanced Calculations

xxxxxxxxxx
DATA risk_metrics;
    SET loan_data;
    
    /* Moving averages */
    IF _N_ >= 3 THEN DO;
        moving_avg_3m = MEAN(OF payment_amt{1:3});
    END;
    
    /* Retain variables across observations */
    RETAIN previous_balance 0;
    balance_change = current_balance - previous_balance;
    previous_balance = current_balance;
    
    /* Arrays for multiple calculations */
    ARRAY payments{12} payment1-payment12;
    total_payments = SUM(OF payments{*});
    avg_payment = MEAN(OF payments{*});
run;

Common Functions for Credit Risk Analysis:

xxxxxxxxxx
DATA risk_analysis;
    SET credit_data;
    
    /* Statistical functions */
    payment_std = STD(OF payment1-payment12);
    payment_max = MAX(OF payment1-payment12);
    payment_min = MIN(OF payment1-payment12);
    
    /* Probability functions */
    normal_score = PROBNORM((credit_score - 700)/100);
    
    /* Rounding functions */
    rounded_score = ROUND(risk_score, 0.5);
    ceiling_value = CEIL(calculated_risk);
    floor_value = FLOOR(calculated_risk);
run;

Best Practices:

1. Always initialize variables:

xxxxxxxxxx
LENGTH new_var $50;  /* For character variables */
new_numeric = 0;     /* For numeric variables */

2. Use meaningful variable names

3. Document calculations

4. Consider missing values:

xxxxxxxxxx
IF NOT MISSING(income) AND NOT MISSING(debt) THEN
    debt_ratio = debt / income;

Common Pitfalls to Avoid:

1. Not handling missing values

2. Not declaring variable lengths for character variables

3. Not considering the order of operations

4. Forgetting to format new variables

5. Not checking for division by zero

Rename a column in-place or otherwise

Renaming Columns in SAS

There are several methods to rename columns in SAS:

1. Using RENAME= dataset option

2. Using RENAME statement

3. Using PROC DATASETS

4. Using PROC SQL

1. Using RENAME= Dataset Option

xxxxxxxxxx
/* Basic syntax */
DATA new_dataset;
    SET original_dataset(RENAME=(old_name=new_name));
run;

/* Practical example */
DATA credit_analysis;
    SET raw_data(RENAME=(
        cust_id=customer_id 
        cr_score=credit_score
        bal=balance
    ));
run;

2. Using RENAME Statement

xxxxxxxxxx
/* Basic syntax */
DATA new_dataset;
    SET original_dataset;
    RENAME old_name=new_name;
run;

/* Practical example */
DATA risk_profile;
    SET customer_data;
    RENAME customer_number=customer_id 
           risk_points=risk_score;
run;

/* Renaming inplace by creating new column, dropping older column, renaming this new column */
DATA risk_profile;
    SET customer_data;
    new_date = old_date
    DROP old_date
    RENAME new_date = old_date_on_boarding
run;

3. Using PROC DATASETS (In-Place Renaming)

xxxxxxxxxx
/* Basic syntax */
PROC DATASETS LIBRARY=libname NOLIST;
    MODIFY dataset_name;
    RENAME old_name=new_name;
QUIT;

/* Practical example */
PROC DATASETS LIBRARY=WORK NOLIST;
    MODIFY credit_data;
    RENAME customer_num=customer_id
           credit_pts=credit_score
           account_bal=balance;
QUIT;

4. Using PROC SQL

xxxxxxxxxx
/* Basic syntax */
PROC SQL;
    CREATE TABLE new_dataset AS
    SELECT old_name AS new_name
    FROM original_dataset;
QUIT;

/* Practical example */
PROC SQL;
    CREATE TABLE credit_analysis AS
    SELECT customer_num AS customer_id,
           credit_pts AS credit_score,
           account_bal AS balance,
           other_columns /* Other columns remain unchanged */
    FROM raw_credit_data;
QUIT;

Practical Examples in Credit Risk Context:

1. Comprehensive Example with Multiple Renaming Methods:

xxxxxxxxxx
/* Create sample data */
DATA credit_raw;
    input cust_num score bal status $ dt;
    format dt date9.;
    datalines;
1001 720 5000 A 21500
1002 680 3000 B 21500
1003 800 7000 A 21500
;
run;

/* Method 1: Using RENAME= option */
DATA credit_clean;
    SET credit_raw(RENAME=(
        cust_num=customer_id
        score=credit_score
        bal=balance
        dt=review_date
    ));
run;

/* Method 2: Using RENAME statement */
DATA credit_clean2;
    SET credit_raw;
    RENAME cust_num=customer_id
           score=credit_score
           bal=balance
           dt=review_date;
run;

/* Method 3: In-place renaming */
PROC DATASETS LIBRARY=WORK NOLIST;
    MODIFY credit_raw;
    RENAME cust_num=customer_id
           score=credit_score
           bal=balance
           dt=review_date;
QUIT;

/* Method 4: Using PROC SQL */
PROC SQL;
    CREATE TABLE credit_clean3 AS
    SELECT cust_num AS customer_id,
           score AS credit_score,
           bal AS balance,
           status,
           dt AS review_date
    FROM credit_raw;
QUIT;

2. Renaming Multiple Variables with Pattern:

xxxxxxxxxx
/* For variables with similar patterns */
DATA credit_history;
    SET monthly_data(RENAME=(
        bal1=balance_month1
        bal2=balance_month2
        bal3=balance_month3
        pay1=payment_month1
        pay2=payment_month2
        pay3=payment_month3
    ));
run;

3. Combining with Other Operations:

xxxxxxxxxx
/* Renaming while selecting specific variables */
DATA risk_analysis;
    SET customer_master(
        RENAME=(
            cust_id=customer_id
            risk_sc=risk_score
        )
        KEEP=cust_id risk_sc balance status
    );
run;

Best Practices:

1. Consistent Naming Convention

xxxxxxxxxx
/* Use consistent naming patterns */
DATA consistent_names;
    SET raw_data(RENAME=(
        cust_id=customer_id        /* Use full words */
        fname=first_name           /* Avoid abbreviations */
        lname=last_name
        ph_num=phone_number
    ));
run;

2. Documentation

xxxxxxxxxx
/* Document rename operations */
DATA credit_clean;
    SET credit_raw(RENAME=(
        cust_num=customer_id    /* Primary identifier */
        scr=credit_score        /* Standardized score name */
        dt=review_date          /* Consistent date naming */
    ));
    /* Add rename explanation */
    ATTRIB customer_id LABEL="Unique Customer Identifier"
           credit_score LABEL="Credit Score (300-850 range)"
           review_date LABEL="Latest Review Date";
run;

Common Pitfalls to Avoid:

1. Not checking if new names already exist

2. Using reserved words as new names

3. Not maintaining proper variable lengths after renaming

4. Forgetting to rename related variables consistently

Quick Reference Table:

Change formatting of a column for existing dataset, or while at creation/appending/merge

• Text to Number

• Number to Text

• A string/number denoting date in one format to another string/number denoting that date in other format

• Text to Date

• Number to Date

• Date to Text

• Date to Number

Format Conversions in SAS

Basic Methods to Change Formats:

1. Using FORMAT statement

2. Using INPUT/PUT functions

3. Using ATTRIB statement

4. Using PROC DATASETS

1. Text to Number Conversions

xxxxxxxxxx
DATA conversion_example;
    /* Sample string data */
    input text_value $20.;
    datalines;
1234
5678.90
-123.45
;

    /* Method 1: Using INPUT function */
    number1 = INPUT(text_value, 8.);
    
    /* Method 2: Using INPUT with specific format */
    number2 = INPUT(text_value, COMMA10.2);
    
    /* Method 3: Best practice with error handling */
    if _ERROR_=0 then
        number3 = INPUT(COMPRESS(text_value), BEST12.);
run;

2. Number to Text Conversions

xxxxxxxxxx
DATA string_conversion;
    /* Sample numeric data */
    input number 8.;
    datalines;
1234
5678.90
-123.45
;

    /* Method 1: Using PUT function */
    text_value1 = PUT(number, 8.);
    
    /* Method 2: Using PUT with specific format */
    text_value2 = PUT(number, DOLLAR10.2);
    
    /* Method 3: With leading zeros */
    text_value3 = PUT(number, Z6.);
run;

3. Date Format Conversions

xxxxxxxxxx
DATA date_conversions;
    /* Various date inputs */
    input date_str $10. number_date 8.;
    datalines;
01JAN2024 20240101
15FEB2024 20240215
31DEC2023 20231231
;

    /* String to Date */
    date1 = INPUT(date_str, DATE9.);
    
    /* Number to Date */
    date2 = INPUT(PUT(number_date, 8.), YYMMDD8.);
    
    /* Date to String */
    date_text1 = PUT(date1, DATE9.);
    date_text2 = PUT(date1, DDMMYY10.);
    
    /* Date to Number */
    date_num = INPUT(PUT(date1, DATE9.), YYMMDD8.);
    
    /* Format specifications */
    FORMAT date1 date2 DATE9.
           date_num YYMMDD10.;
run;

4. Comprehensive Credit Risk Example

xxxxxxxxxx
/* Create sample credit data with mixed formats */
DATA credit_raw;
    input customer_id $ 
          credit_score $ 
          review_date $ 
          balance $ 
          status $;
    datalines;
A12345 720 20240115 5000.00 ACTIVE
B67890 680 20240120 3500.50 REVIEW
C11111 800 20240125 7500.75 ACTIVE
;
run;

/* Comprehensive format conversion */
DATA credit_clean;
    SET credit_raw;
    
    /* Convert credit score from string to numeric */
    credit_score_num = INPUT(credit_score, 8.);
    
    /* Convert balance from string to numeric */
    balance_num = INPUT(balance, COMMA10.2);
    
    /* Convert date string to SAS date */
    review_date_sas = INPUT(review_date, YYMMDD8.);
    
    /* Create formatted strings */
    formatted_balance = PUT(balance_num, DOLLAR12.2);
    formatted_date = PUT(review_date_sas, DATE9.);
    
    /* Format specifications */
    FORMAT review_date_sas DATE9.
           balance_num DOLLAR12.2
           credit_score_num 8.;
run;

5. Changing Formats for Existing Dataset

xxxxxxxxxx
/* Method 1: Using PROC DATASETS */
PROC DATASETS LIBRARY=WORK NOLIST;
    MODIFY credit_clean;
    FORMAT balance_num DOLLAR12.2
           review_date_sas MMDDYY10.
           credit_score_num 4.;
QUIT;

/* Method 2: Using DATA step */
DATA credit_clean;
    SET credit_clean;
    FORMAT balance_num DOLLAR12.2
           review_date_sas MMDDYY10.
           credit_score_num 4.;
run;

6. Common Date Format Conversions

xxxxxxxxxx
DATA date_examples;
    /* Various date format scenarios */
    
    /* String dates in different formats */
    date_str1 = '01JAN2024';
    date_str2 = '2024-01-01';
    date_str3 = '01/01/2024';
    
    /* Convert to SAS dates */
    sas_date1 = INPUT(date_str1, DATE9.);
    sas_date2 = INPUT(date_str2, YYMMDD10.);
    sas_date3 = INPUT(date_str3, MMDDYY10.);
    
    /* Convert to different formats */
    formatted_date1 = PUT(sas_date1, DATE9.);
    formatted_date2 = PUT(sas_date1, DDMMYY10.);
    formatted_date3 = PUT(sas_date1, YYMMDD10.);
    formatted_date4 = PUT(sas_date1, WEEKDATE.);
    
    /* Numeric representation */
    date_num = INPUT(PUT(sas_date1, DATE9.), YYMMDD8.);
    
    FORMAT sas_date1-sas_date3 DATE9.;
run;

Common Format Types:

xxxxxxxxxx
/* Numeric Formats */
FORMAT numeric_var 
    BEST12.        /* Best representation */
    COMMA10.2      /* With commas and decimals */
    DOLLAR12.2     /* Currency format */
    PERCENT8.2     /* Percentage format */
    Z6.            /* Leading zeros */
;

/* Date Formats */
FORMAT date_var 
    DATE9.         /* Default date format */
    DATETIME20.    /* Date and time */
    DDMMYY10.      /* DD/MM/YYYY */
    MMDDYY10.      /* MM/DD/YYYY */
    YYMMDD10.      /* YYYY-MM-DD */
    WEEKDATE.      /* Full week date */
;

Best Practices:

1. Always validate conversions:

xxxxxxxxxx
/* Validation example */
DATA _NULL_;
    converted_value = INPUT('123.45', 8.);
    IF _ERROR_ THEN PUT 'ERROR: Invalid conversion';
run;

2. Handle missing values:

xxxxxxxxxx
DATA clean_data;
    SET raw_data;
    IF NOT MISSING(string_value) THEN
        numeric_value = INPUT(string_value, 8.);
run;

3. Document format changes:

xxxxxxxxxx
/* Add labels and formats */
PROC DATASETS LIBRARY=WORK NOLIST;
    MODIFY dataset_name;
    LABEL variable1='Description of variable1'
          variable2='Description of variable2';
    FORMAT variable1 FORMAT1.
           variable2 FORMAT2.;
QUIT;

Formats for string, number, date in SAS. How is date stored as and how to handle it

1. String (Character) Formats

xxxxxxxxxx
/* Basic string formats */
FORMAT 
    name $12.         /* Fixed width character */
    address $UPCASE.  /* Uppercase */
    city $PROPCASE.   /* Proper case */
    state $2.         /* Two-character state code */
;

/* Example usage */
DATA string_formats;
    input text $20.;
    datalines;
JOHN SMITH
new york city
123 Main St
;

    /* Different string formats */
    name_upper = PUT(text, $UPCASE12.);
    name_proper = PUT(text, $PROPCASE20.);
    name_left = PUT(text, $LEFT12.);
    name_right = PUT(text, $RIGHT12.);
run;

2. Numeric Formats

xxxxxxxxxx
/* Basic numeric formats */
FORMAT 
    amount DOLLAR12.2    /* $1,234.56 */
    rate PERCENT8.2      /* 12.34% */
    score BEST12.        /* Best representation */
    id Z6.              /* Leading zeros */
    decimal 8.2         /* Fixed decimal */
    comma COMMA10.2     /* With thousands separator */
;

/* Example usage */
DATA numeric_formats;
    input value;
    datalines;
1234.56
0.1234
123456
;

    /* Different numeric formats */
    FORMAT 
        value_dollar DOLLAR12.2
        value_percent PERCENT8.2
        value_comma COMMA10.2
        value_scientific E12.
        value_binary BINARY8.
    ;
run;

3. Date Formats and Storage

How SAS Stores Dates:

• SAS stores dates as numbers representing days since January 1, 1960

• Negative numbers represent dates before 1960

• Time is stored as number of seconds since midnight

• Datetime is stored as seconds since January 1, 1960

xxxxxxxxxx
/* Understanding date storage */
DATA date_storage;
    /* Create various date values */
    date1 = '01JAN2024'd;  /* Date constant */
    date2 = TODAY();       /* Current date */
    time1 = TIME();        /* Current time */
    dt1 = DATETIME();      /* Current datetime */
    
    /* Show raw numeric values */
    raw_date = date1;      /* Days since 1960 */
    raw_time = time1;      /* Seconds since midnight */
    raw_dt = dt1;          /* Seconds since 1960 */
    
    /* Format specifications */
    FORMAT 
        date1 date2 DATE9.
        time1 TIME8.
        dt1 DATETIME20.
        raw_date raw_time raw_dt BEST12.
    ;
run;

4. Common Date Formats

xxxxxxxxxx
/* Comprehensive date format example */
DATA date_formats;
    date_value = '01JAN2024'd;
    
    /* Different date formats */
    FORMAT 
        date1 DATE9.        /* 01JAN2024 */
        date2 DDMMYY10.     /* 01/01/2024 */
        date3 MMDDYY10.     /* 01/01/2024 */
        date4 YYMMDD10.     /* 2024-01-01 */
        date5 WEEKDATE.     /* Monday, January 1, 2024 */
        date6 MONYY7.       /* JAN2024 */
        date7 YEAR4.        /* 2024 */
        date8 WORDDATX.     /* January 1, 2024 */
    ;
    
    /* Assign same date with different formats */
    date1 = date_value;
    date2 = date_value;
    date3 = date_value;
    date4 = date_value;
    date5 = date_value;
    date6 = date_value;
    date7 = date_value;
    date8 = date_value;
run;

5. Date Handling and Calculations

xxxxxxxxxx
/* Date manipulation examples */
DATA date_handling;
    /* Create some dates */
    start_date = '01JAN2024'd;
    end_date = '31DEC2024'd;
    
    /* Basic calculations */
    days_between = end_date - start_date;
    years_between = YRDIF(start_date, end_date, 'AGE');
    months_between = INTCK('MONTH', start_date, end_date);
    
    /* Add intervals */
    next_month = INTNX('MONTH', start_date, 1);
    next_quarter = INTNX('QTR', start_date, 1);
    next_year = INTNX('YEAR', start_date, 1);
    
    /* Beginning and end of periods */
    month_start = INTNX('MONTH', start_date, 0, 'B');
    month_end = INTNX('MONTH', start_date, 0, 'E');
    
    /* Extract components */
    year = YEAR(start_date);
    month = MONTH(start_date);
    day = DAY(start_date);
    
    /* Format specifications */
    FORMAT start_date end_date 
           next_month next_quarter next_year
           month_start month_end DATE9.;
run;

6. Datetime and Time Formats

xxxxxxxxxx
/* Time and datetime handling */
DATA time_formats;
    current_dt = DATETIME();
    current_time = TIME();
    
    /* Different time formats */
    FORMAT 
        time1 TIME8.        /* 14:30:00 */
        time2 TIMEAMPM8.    /* 2:30 PM */
        time3 HHMM5.        /* 14:30 */
        
        dt1 DATETIME20.     /* 01JAN2024:14:30:00 */
        dt2 DTDATE9.        /* 01JAN2024 */
        dt3 DTWKDATX.       /* Monday, January 1, 2024 */
    ;
    
    time1 = current_time;
    time2 = current_time;
    time3 = current_time;
    
    dt1 = current_dt;
    dt2 = current_dt;
    dt3 = current_dt;
run;

7. Best Practices for Date Handling

xxxxxxxxxx
/* Date handling best practices */
DATA date_best_practices;
    /* 1. Always validate date inputs */
    input_date = '2024-01-01';
    valid_date = INPUT(input_date, YYMMDD10.);
    IF NOT MISSING(valid_date);
    
    /* 2. Handle missing dates */
    IF MISSING(valid_date) THEN valid_date = .;
    
    /* 3. Use date constants carefully */
    fixed_date = '01JAN2024'd;
    
    /* 4. Use relative dates */
    today = TODAY();
    yesterday = TODAY() - 1;
    last_month = INTNX('MONTH', TODAY(), -1);
    
    /* 5. Standard format for storage */
    FORMAT valid_date fixed_date 
           today yesterday last_month DATE9.;
run;

Common Date Functions Reference:

Length, Format, Informat

1. LENGTH Statement

Used to specify the number of bytes for storing variables.

xxxxxxxxxx
/* Basic LENGTH syntax */
DATA example;
    LENGTH 
        customer_id 8      /* Numeric, 8 bytes */
        name $50          /* Character, 50 bytes */
        address $100      /* Character, 100 bytes */
        balance 8         /* Numeric, 8 bytes */
    ;
run;

2. FORMAT Statement

Specifies how values are displayed when printed or viewed.

xxxxxxxxxx
/* Basic FORMAT syntax */
DATA example;
    FORMAT 
        balance DOLLAR12.2    /* $1,234.56 */
        rate PERCENT8.2       /* 12.34% */
        date DATE9.           /* 01JAN2024 */
        name $50.             /* Character format */
    ;
run;

3. INFORMAT Statement

Specifies how SAS should read input values.

xxxxxxxxxx
/* Basic INFORMAT syntax */
DATA example;
    INFORMAT 
        date YYMMDD10.        /* Reads 2024-01-01 */
        balance DOLLAR12.2    /* Reads $1,234.56 */
        text $50.             /* Reads character */
    ;
run;

Comprehensive Example Using All Three

xxxxxxxxxx
/* Create sample credit risk data */
DATA credit_data;
    /* Define lengths */
    LENGTH 
        customer_id $10    /* Character ID */
        name $50          /* Customer name */
        status $10        /* Account status */
        notes $200        /* Comments field */
        credit_score 8    /* Numeric score */
        balance 8         /* Account balance */
        review_date 8     /* Date field */
    ;
    
    /* Define formats for display */
    FORMAT 
        credit_score BEST12.
        balance DOLLAR12.2
        review_date DATE9.
        status $UPCASE.
    ;
    
    /* Define informats for reading */
    INFORMAT 
        credit_score BEST12.
        balance COMMA12.2
        review_date YYMMDD10.
        status $UPCASE.
    ;
    
    /* Sample data */
    input customer_id $ name $ credit_score balance review_date YYMMDD10. 
          status $;
    datalines;
C001 John_Smith 720 5000.50 2024-01-15 active
C002 Mary_Jones 680 3500.75 2024-01-16 review
C003 Bob_Wilson 800 7500.25 2024-01-17 active
;
run;

Common Format Types Reference Table

Advanced Usage Examples

1. Complex Data Structure

xxxxxxxxxx
/* Creating a complex credit risk dataset */
DATA risk_analysis;
    /* Length definitions */
    LENGTH 
        account_id $15        /* Account identifier */
        customer_name $50     /* Full name */
        risk_category $10     /* Risk classification */
        review_notes $500     /* Detailed notes */
        credit_score 8        /* Credit score */
        default_prob 8        /* Default probability */
        last_review 8         /* Review date */
        next_review 8         /* Next review date */
    ;
    
    /* Format specifications */
    FORMAT 
        credit_score BEST12.
        default_prob PERCENT8.2
        last_review DDMMYY10.
        next_review DDMMYY10.
        risk_category $UPCASE.
    ;
    
    /* Informat specifications */
    INFORMAT 
        credit_score BEST12.
        default_prob PERCENT8.
        last_review YYMMDD10.
        next_review YYMMDD10.
        risk_category $UPCASE.
    ;
run;

2. Using ATTRIB Statement (Combines LENGTH, FORMAT, and INFORMAT)

xxxxxxxxxx
DATA credit_profile;
    /* ATTRIB combines LENGTH, FORMAT, and INFORMAT */
    ATTRIB 
        customer_id   LENGTH=$10    
                     LABEL='Customer Identifier'
        
        credit_score LENGTH=8       
                     FORMAT=BEST12. 
                     LABEL='Credit Score (300-850)'
        
        balance      LENGTH=8       
                     FORMAT=DOLLAR12.2 
                     LABEL='Current Balance'
        
        review_date  LENGTH=8       
                     FORMAT=DATE9. 
                     INFORMAT=YYMMDD10. 
                     LABEL='Last Review Date'
        
        status       LENGTH=$10     
                     FORMAT=$UPCASE. 
                     LABEL='Account Status'
    ;
run;

3. Handling Special Cases

xxxxxxxxxx
DATA special_cases;
    /* Length for efficiency */
    LENGTH 
        long_text $32767    /* Maximum length for character */
        small_num 3         /* Small numeric (3 bytes) */
        precise_num 8       /* Full precision (8 bytes) */
    ;
    
    /* Special formats */
    FORMAT 
        small_num Z5.       /* Leading zeros */
        precise_num 16.8    /* High precision */
        percent_val PERCENT8.1  /* One decimal percentage */
    ;
    
    /* Special informats */
    INFORMAT 
        date_var ANYDTDTE.  /* Flexible date reading */
        num_var COMMA12.    /* Reads numbers with commas */
    ;
run;

Best Practices

1. Define Lengths Early

xxxxxxxxxx
/* Define lengths at the start of DATA step */
DATA customer_data;
    LENGTH customer_id $10 name $50 balance 8;
    /* Rest of the code */
run;

2. Use Consistent Formats

xxxxxxxxxx
/* Standardize formats across program */
PROC FORMAT;
    VALUE $risk_fmt 
        'H' = 'High Risk'
        'M' = 'Medium Risk'
        'L' = 'Low Risk';
run;

DATA risk_data;
    FORMAT risk_level $risk_fmt.;
    /* Rest of the code */
run;

3. Document with Labels

xxxxxxxxxx
DATA documented_data;
    ATTRIB 
        customer_id LENGTH=$10 LABEL='Unique Customer ID'
        balance LENGTH=8 FORMAT=DOLLAR12.2 
                LABEL='Current Account Balance'
    ;
run;

Put, Input, Putn, Inputn and their usage within macro function context

1. Basic Functions Overview

xxxxxxxxxx
/* Sample data for demonstrations */
DATA sample;
    /* Numeric values */
    num_val = 12345.67;
    
    /* Character values */
    char_val = '12345.67';
    
    /* Date value */
    date_val = '01JAN2024'd;
    
    /* Basic conversions */
    /* Numeric to Character */
    char_from_num = PUT(num_val, 8.2);
    
    /* Character to Numeric */
    num_from_char = INPUT(char_val, 8.2);
    
    /* Numeric to Numeric with format */
    num_formatted = PUTN(num_val, 'DOLLAR12.2');
    
    /* Character to Numeric with format */
    num_from_char2 = INPUTN(char_val, 'COMMA12.2');
run;

2. PUT vs PUTN

xxxxxxxxxx
DATA put_examples;
    num_value = 12345.67;
    date_value = '01JAN2024'd;
    
    /* PUT - returns character */
    char_result1 = PUT(num_value, 12.2);        /* '12345.67' */
    char_result2 = PUT(num_value, DOLLAR12.2);  /* '$12,345.67' */
    char_result3 = PUT(date_value, DATE9.);     /* '01JAN2024' */
    
    /* PUTN - returns numeric */
    num_result1 = PUTN(num_value, '12.2');
    num_result2 = PUTN(date_value, 'DATE9.');
    
    /* Verify results */
    PUT "Character Results:";
    PUT char_result1= char_result2= char_result3=;
    PUT "Numeric Results:";
    PUT num_result1= num_result2=;
run;

3. INPUT vs INPUTN

xxxxxxxxxx
DATA input_examples;
    char_value = '12345.67';
    date_str = '01JAN2024';
    
    /* INPUT - flexible, can return character or numeric */
    num_result1 = INPUT(char_value, 12.2);
    date_result = INPUT(date_str, DATE9.);
    
    /* INPUTN - always returns numeric */
    num_result2 = INPUTN(char_value, '12.2');
    date_result2 = INPUTN(date_str, 'DATE9.');
    
    /* Verify results */
    PUT "Results from INPUT:";
    PUT num_result1= date_result=;
    PUT "Results from INPUTN:";
    PUT num_result2= date_result2=;
run;

4. Macro Context Usage

xxxxxxxxxx
/* Macro variable creation and manipulation */
%macro format_conversions;
    /* Create some macro variables */
    %let numeric_value = 12345.67;
    %let char_value = 12345.67;
    %let date_value = 01JAN2024;
    
    /* Using PUT in macro */
    %let formatted_num = %sysfunc(PUT(&numeric_value, DOLLAR12.2));
    
    /* Using PUTN in macro */
    %let num_formatted = %sysfunc(PUTN(&numeric_value, DOLLAR12.2));
    
    /* Using INPUT in macro */
    %let parsed_num = %sysfunc(INPUT(&char_value, 12.2));
    
    /* Using INPUTN in macro */
    %let parsed_date = %sysfunc(INPUTN(&date_value, DATE9.));
    
    /* Display results */
    %put Formatted Number (PUT): &formatted_num;
    %put Numeric Formatted (PUTN): &num_formatted;
    %put Parsed Number (INPUT): &parsed_num;
    %put Parsed Date (INPUTN): &parsed_date;
%mend;

%format_conversions;

5. Complex Examples in Credit Risk Context

xxxxxxxxxx
/* Credit risk scoring with macro processing */
%macro process_credit_data;
    /* Sample credit data */
    DATA credit_raw;
        input account_id $ score $ balance $ review_date $;
        datalines;
    A001 720 5000.00 01JAN2024
    A002 680 3500.50 15JAN2024
    A003 800 7500.75 31JAN2024
    ;
    run;
    
    /* Process using various conversion functions */
    DATA credit_processed;
        SET credit_raw;
        
        /* Convert score to numeric */
        score_num = INPUTN(score, 'BEST12.');
        
        /* Convert balance to numeric with format */
        balance_num = INPUTN(balance, 'COMMA12.2');
        
        /* Convert date string to SAS date */
        review_date_sas = INPUT(review_date, DATE9.);
        
        /* Create formatted strings for reporting */
        score_cat = PUT(score_num, 
            CASE.
                WHEN (score_num >= 750) 'Excellent'
                WHEN (score_num >= 700) 'Good'
                WHEN (score_num >= 650) 'Fair'
                OTHERWISE 'Poor'
            );
        
        /* Format balance for display */
        balance_fmt = PUT(balance_num, DOLLAR12.2);
        
        /* Format date for reporting */
        review_date_fmt = PUT(review_date_sas, DATE9.);
    run;
    
    /* Create macro variables for reporting */
    PROC SQL NOPRINT;
        SELECT 
            PUT(AVG(score_num), 5.1),
            PUT(SUM(balance_num), DOLLAR12.2)
        INTO
            :avg_score,
            :total_balance
        FROM credit_processed;
    QUIT;
    
    %put Average Score: &avg_score;
    %put Total Balance: &total_balance;
%mend;

%process_credit_data;

6. Error Handling Examples

xxxxxxxxxx
%macro safe_conversions;
    /* Safe numeric conversion */
    %let raw_value = abc123;
    
    /* Using INPUT with error checking */
    %let numeric_result = %sysfunc(INPUTN(&raw_value, BEST12.));
    %if %sysfunc(missing(&numeric_result)) %then %do;
        %put ERROR: Invalid numeric conversion for &raw_value;
    %end;
    
    /* Safe date conversion */
    %let date_str = 32JAN2024;
    
    /* Using INPUTN with date format */
    %let date_result = %sysfunc(INPUTN(&date_str, DATE9.));
    %if %sysfunc(missing(&date_result)) %then %do;
        %put ERROR: Invalid date conversion for &date_str;
    %end;
%mend;

%safe_conversions;