Ora

How to Create a Date Column in SAS

Published in SAS Date Handling 6 mins read

Creating a date column in SAS is fundamental for time-series analysis, data filtering, and reporting. SAS stores dates as numeric values representing the number of days since January 1, 1960. To display these numeric values as recognizable dates, you apply a date format. This guide will walk you through various methods to construct date columns in your SAS datasets.

Understanding SAS Date Values and Formats

Before diving into creation methods, it's crucial to grasp how SAS handles dates:

  • SAS Date Value: A numeric value representing the number of days from January 1, 1960, to a specific date. For example, January 2, 1960, is stored as 1, and January 1, 1959, is stored as -365.
  • SAS Date Format: An instruction that tells SAS how to display a numeric SAS date value as a readable date string (e.g., MMDDYY10., DATE9.). Without a format, a SAS date value will appear as a plain number.
  • SAS Date Informat: An instruction that tells SAS how to read a date from a character string and convert it into a numeric SAS date value.

Methods for Creating a Date Column

Here are the most common ways to create a date column in SAS, complete with examples:

1. Combining Month, Day, and Year Variables Using the MDY Function

This method is ideal when your date information is split across separate variables for month, day, and year. The MDY function combines these numeric components into a single SAS date value.

data mydata;
    input mn days yr value;
    datalines;
    1 15 2023 100
    3 01 2024 150
    12 25 2022 200
    ;
run;

data mydata_with_date;
    set mydata;
    /* Create a new variable 'transaction_date' by combining mn, days, and yr */
    /* The MDY function takes month, day, year as arguments */
    transaction_date = mdy(mn, days, yr);

    /* Apply a format to display the date in MM/DD/YYYY format */
    /* MMDDYY10. is a common format to display dates as MM/DD/YYYY */
    format transaction_date MMDDYY10.;
run;

proc print data=mydata_with_date;
    title "Dataset with New Date Column from MDY";
run;

Explanation:

  • The MDY(month, day, year) function takes three numeric arguments: month, day, and year. It returns the corresponding SAS date value.
  • The format transaction_date MMDDYY10.; statement tells SAS to display the transaction_date variable in the MM/DD/YYYY format. You can choose other formats like DATE9. (e.g., 15JAN2023) or DDMMYY10. (e.g., 15/01/2023).

2. Converting Character Strings to Dates Using the INPUT Function

When your date is stored as a character string (e.g., '2023-01-15', 'January 15, 2023'), you use the INPUT function with an appropriate informat to convert it into a SAS date value.

data sales_data;
    input char_date $ sales_amount;
    datalines;
    '2023-01-15' 1200
    '03/01/2024' 1550
    '25DEC2022'  1800
    '1/1/2023'   1000
    ;
run;

data sales_data_with_date;
    set sales_data;
    /* Convert character date string to SAS date value */
    /* The INPUT function uses the character string and an informat */
    /* YYYYMMDD10. informat for 'YYYY-MM-DD' */
    /* MMDDYY10. informat for 'MM/DD/YYYY' */
    /* DATE9. informat for 'DDMONYYYY' */
    /* MMDDYY8. informat for 'M/D/YYYY' or 'MM/DD/YY' */

    if char_date = '2023-01-15' then order_date = input(char_date, YYYYMMDD10.);
    else if char_date = '03/01/2024' then order_date = input(char_date, MMDDYY10.);
    else if char_date = '25DEC2022' then order_date = input(char_date, DATE9.);
    else if char_date = '1/1/2023' then order_date = input(char_date, MMDDYY8.);
    else order_date = .; /* Handle cases not matching specified formats */

    /* Apply a format for display */
    format order_date DATE9.;
run;

proc print data=sales_data_with_date;
    title "Dataset with New Date Column from Character String";
run;

Practical Insight: Often, character date strings are consistent in format within a column. In such cases, a single INPUT function call will suffice. If they vary, you might need conditional logic (IF-THEN-ELSE) or multiple INPUT statements with different informats.

3. Converting Numeric Values to Dates

Sometimes, dates might be stored as numeric values, but not as SAS date values (e.g., 20230115 as a number). You'll need to first convert this numeric value to a character string, then use the INPUT function.

data raw_data;
    input numeric_date_id amount;
    datalines;
    20230115 500
    20240301 750
    20221225 900
    ;
run;

data processed_data_with_date;
    set raw_data;
    /* Convert numeric_date_id to a character string */
    char_date_str = put(numeric_date_id, 8.); /* Use PUT function to convert number to char */

    /* Then convert the character string to a SAS date value */
    reporting_date = input(char_date_str, YYYYMMDD8.);

    /* Apply a format for display */
    format reporting_date MMDDYY10.;
run;

proc print data=processed_data_with_date;
    title "Dataset with New Date Column from Numeric ID";
run;

Explanation:

  • put(numeric_date_id, 8.) converts the numeric 20230115 into the character string '20230115'. The 8. format specifies the length of the resulting character string.
  • input(char_date_str, YYYYMMDD8.) then reads this character string using the YYYYMMDD8. informat and converts it into a SAS date value.

4. Creating a Date Column with the Current Date

To add a column that reflects the date when the SAS program is run, use the TODAY() function.

data current_day_report;
    /* The TODAY() function returns the current date as a SAS date value */
    report_date = today();

    /* Apply a format for display */
    format report_date DATE9.;

    /* Add some dummy data for illustration */
    metric_value = 123.45;
run;

proc print data=current_day_report;
    title "Dataset with Current Date Column";
run;

Common SAS Date Formats and Informats

Choosing the correct format or informat is crucial. Here's a quick reference for some widely used options:

Format/Informat Description Example Input String (for Informat) Example Output (for Format)
MMDDYY10. MM/DD/YYYY '01/15/2023' 01/15/2023
DDMMYY10. DD/MM/YYYY '15/01/2023' 15/01/2023
YYYYMMDD10. YYYY-MM-DD '2023-01-15' 2023-01-15
DATE9. DDMMMYYYY '15JAN2023' 15JAN2023
MONYY7. MMMYY 'JAN23' JAN23
YYMMDD8. YYMMDD '230115' 230115

For a comprehensive list, refer to the SAS documentation on Date and Time Formats and Informats.

Why Use SAS Date Values?

  • Calculations: You can easily perform date arithmetic (e.g., date1 - date2 to get the number of days between two dates, or INTNX('MONTH', date1, 1) to get the start of the next month).
  • Sorting and Filtering: Dates sort correctly numerically, allowing for straightforward filtering by range.
  • Consistency: Standardized SAS date values ensure consistency across your analyses, regardless of how the original data was represented.

By mastering these methods, you can effectively create and manage date columns in your SAS datasets, unlocking powerful analytical capabilities.

← Previous Article
How big are the Indonesian water dragons?
Next Article →
Are There Orange Cardinals?