topic Re: How to convert VARCHAR column containing unusual datetimes into Numeric MM/DD/YYYY in CAS in SAS Programming

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

Gary_Z — Thu, 20 Apr 2023 14:21:58 GMT

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.

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

PaigeMiller — Thu, 20 Apr 2023 14:54:54 GMT

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

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

Tom — Thu, 20 Apr 2023 17:21:44 GMT

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.

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

ballardw — Thu, 20 Apr 2023 22:07:07 GMT

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.

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

Gary_Z — Mon, 24 Apr 2023 12:54:13 GMT

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

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

Tom — Mon, 24 Apr 2023 14:44:38 GMT

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.