Wednesday, May 3, 2017

Fixing SAS Dates (SAS)

It is no secret that SAS’s ability at handling date formatting is truly woesome. To help deal with this shortcoming, I have devised a universal method to clean and format SAS dates.

For this method to work, you must first format the date variable so that it resembles the following format:

MM/DD/YYYY

The variable must be a string type.

In our example, we will be modifying a date variable that contains an un-necessary time stamp.


The code below first creates new additional variables which will eventually be pieced together to form the final date variable. Each new variable will contain a portion of the original date (Month, Day, and Year).

Data SetB;

Length Month $ 2; /* Sets length for Month variable */

Length Day $ 2; /* Sets lenght for Day variable */

Length Year $ 4; /* Sets lenght for Year variable */

Set SetA;

Once this has been completed, our code will then scan the original date variable for the data that will be stored in the newly created variables.

Year = SCAN(Date1, 3 , '/'); /* Creates the year variable from the date variable */

Month = SCAN(Date1, 1, '/'); /* Creates the year variable from the date variable */

Day = SCAN(Date1, 2, '/'); /* Creates the year variable from the date variable */

Next, another new variable is created that combines all of the newly created variables (Month, Day, Year) into one single string variable.

DateFix = STRIP(Month) || '/' || STRIP(Day) || '/'|| Year ; /* Stips the blank space data from the Month and Day variables, then concatenates Month, Day and Year into a new variable */

This final bit of code creates yet another variable which will house the formatted date variable after it is read and converted from the "DateFix" variable.

NewDate = input(DateFix, mmddyy10.); /* Formats the new variable */

Format NewDate date9.; /* Formats the new variable */

Finally, we will drop all the un-necessary variables that were created for this project.

Drop Month Day Year DateFix; /* After the re-formatting, there is no need to mantain these variables */

Run;

The final product of this endeavor will resemble:


Here is the code in its entirety:

Data SetB; 

Length Month $ 2; /* Sets length for Month variable */

Length Day $ 2; /* Sets length for Day variable */

Length Year $ 4; /* Sets length for Year variable */

Set SetA; 

Year = SCAN(Date1, 3 , '/'); /* Creates the year variable from the date variable */

Month = SCAN(Date1, 1, '/'); /* Creates the year variable from the date variable */

Day = SCAN(Date1, 2, '/'); /* Creates the year variable from the date variable */

DateFix = STRIP(Month) || '/' || STRIP(Day) || '/'|| Year ; /* Stips the blank space data from the Month and Day variables,  then concatenates Month, Day and Year into a new variable */

NewDate = input(DateFix, mmddyy10.); /* Formats the new variable */

Format NewDate date9.; /* Formats the new variable */

Drop Month Day Year DateFix; /* After the re-formatting, there is no need to maintain these variables */

Run;

For the portion of code that is listed:

Format NewDate date9.; /* Formats the new variable */

You are not restricted to the use of the date9. format. Any format of your choice can be utilized for date formatting purposes. A list of potential date formats can be found by clicking the link below:

SAS University Date Formats

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.