How to convert VARCHAR column containing unusual datetimes into Numeric MM/DD/YYYY in CAS

[Gary_Z]

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. 

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. 

[PaigeMiller]

---

@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. 

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;

[Tom]

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]

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]

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]

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.