Accepted Solutions
Here's a quick way to do it:
Data Arias.Merge_data_E_18 ;
Set Arias.Merge_data_E_18;
FORMAT DOB mmddyys10.;
run;After you run that code, you should see dates showing in your output data set in the format mm/dd/ccyy, for example: 08/14/2020.
Results using the date values you supplied:
SAS shows you the dates in the format you want, but the data has not changed. This is just a format. What you see is NOT the actual value of the data but rather just a formatted display of the underlying value.
If you actually need a new variable, you could code the following:
Data Arias.Merge_data_E_18 ;
Set Arias.Merge_data_E_18;
New_DOB = PUT(DOB, mmddyys10.);
run;
Results:
Notice the difference in the icons for each column.
- In the first example, there was a little calendar icon for DOB. The calendar icon indicates that the actual data is numeric but that it is being displayed using a date format.
- In the second example, there is a "#" icon for DOB. The "#" sign indicates a numeric variable without a date format. The icon for New_DOB is an "A" icon. The "A" icon indicates alphanumeric (character) data. New_DOB represents an actual change to the data. New_DOB is not a date variable but rather contains character contents as shown. What you see is the actual value of the data.
General rules for PUT and INPUT:
When you want to change or show a numeric variable in a character format, use PUT.
When you want to change or show a character variable in a numeric format, use INPUT.
Your original example was using INPUT on a numeric variable. The general rule is to use PUT on numeric variables and INPUT on character variables.
Jim
Here's a quick way to do it:
Data Arias.Merge_data_E_18 ;
Set Arias.Merge_data_E_18;
FORMAT DOB mmddyys10.;
run;After you run that code, you should see dates showing in your output data set in the format mm/dd/ccyy, for example: 08/14/2020.
Results using the date values you supplied:
SAS shows you the dates in the format you want, but the data has not changed. This is just a format. What you see is NOT the actual value of the data but rather just a formatted display of the underlying value.
If you actually need a new variable, you could code the following:
Data Arias.Merge_data_E_18 ;
Set Arias.Merge_data_E_18;
New_DOB = PUT(DOB, mmddyys10.);
run;
Results:
Notice the difference in the icons for each column.
- In the first example, there was a little calendar icon for DOB. The calendar icon indicates that the actual data is numeric but that it is being displayed using a date format.
- In the second example, there is a "#" icon for DOB. The "#" sign indicates a numeric variable without a date format. The icon for New_DOB is an "A" icon. The "A" icon indicates alphanumeric (character) data. New_DOB represents an actual change to the data. New_DOB is not a date variable but rather contains character contents as shown. What you see is the actual value of the data.
General rules for PUT and INPUT:
When you want to change or show a numeric variable in a character format, use PUT.
When you want to change or show a character variable in a numeric format, use INPUT.
Your original example was using INPUT on a numeric variable. The general rule is to use PUT on numeric variables and INPUT on character variables.
Jim
@wlierman wrote:
I could just drop DOB, but I am really curious now as to how to solve this.
Do you mean the 1861 issue? I think the 1861 is bad data in which case even if we read it in, it will still be wrong. However, not all date issues will be bad data, so let's have a look:
We have this so far:
New_DOB = PUT(INPUT(DOB, 5.), MMDDYYS10.);Which I believe is working fine 99% of the time -- except of course New_DOB sometimes comes up "missing." After the above code, we can do the following:
IF STRIP(New_DOB) = '.' THEN
New_DOB = PUT(INPUT(SUBSTR(DOB, 1, (INDEX(DOB, ' '))), MMDDYY10.), MMDDYYS10.);
We'd then have the following results:
The above code may seem a little complicated, but it does protect us from truly invalid dates, for example 04/32/1861. A truly invalid date will result in a '.' which we may want to translate to a space since a space represents missing for character fields.
Jim
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Click image to register for webinar
Classroom Training Available!
Select SAS Training centers are offering in-person courses. View upcoming courses for:
-
2940 views
-
3 likes
-
-
