BookmarkSubscribeRSS Feed
Gary_Z
Fluorite | Level 6

I am trying to convert a column containing date values in the following format to MM/DD/YYYY.

The values look like this in the raw CSV data. 

Gary_Z_0-1682000009345.png

After loading the raw data into a CAS table, this column is a VARCHAR, I would like to either import the values as numeric MM/DD/YYYY or create a separate column that is a Numeric that only has values in this format: MM/DD/YYYY

Is there a  way to do this as part of a CAS Action? 

Thanks. 

5 REPLIES 5
PaigeMiller
Diamond | Level 26

@Gary_Z wrote:

I am trying to convert a column containing date values in the following format to MM/DD/YYYY.

The values look like this in the raw CSV data. 

Gary_Z_0-1682000009345.png

I would like to either import the values as numeric MM/DD/YYYY


Read these values in, after removing the slashes, using informat yymmdd8.

Then assign whatever format you want to this variable.

 

data want;
    set have;
    day=input(compress(datevalues,'/'),yymmdd8.);
run;
--
Paige Miller
Tom
Super User Tom
Super User

If you only want a DATE value (you want to ignore the time of day part of the strings) then a simple INPUT() function call will work.

datavar = input(stringvar,yymmdd10.);

You can then attach any date type format to the new numeric variable.  I would avoid using a confusing format like MMDDYY10.   Instead use either DATE9. or YYMMDD10. format so that the dates will display in a way that will avoid the confusion that can result from displaying dates in either MDY or DMY order.

Gary_Z
Fluorite | Level 6

Thanks for the solutions. Here is what I ended up doing after reading in the data: 

 

date = compress(report_dat,'/');
date_mmddyyyy = input(date, yymmdd8.);
format date_mmddyyyy MMDDYY10.;



Tom
Super User Tom
Super User

Not sure why you need the COMPRESS() function call.  The YYMMDD informat will happily read strings using the / character between the fields. In fact it will allow any number of different delimiter characters.

ballardw
Super User

You don't mention how you read that into SAS. If you write your own data step to read it then that value could be read with YYMMDD10. informat. The 10 in the informat is how many characters are read so would ignore the time component.

I use this fairly often as I have several sources that insist on providing times with the exciting values of 00:00:00.000 or similar, in other words there is no actual time component.

Assign what ever format you want at that point.

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1457 views
  • 0 likes
  • 4 in conversation