Convert a char to date format

[jorquec]

Hi 

Please could someone help on this issue?

My first column despite being visualized as date ,SAS type is pointing as Char so I would like to format this Char type into a date type in this same format like ddmmyyyy hh:mm.

All my information on DATE_ADDED column is from November 2021, just the day and hour changes .

Examples

DATE_ADDED

01/11/2021 00:21

Char

16

02/11/2021 13:41

03/11/2021 13:31

I've tried

data work.cla;
set work.O2_Student_B;
dt_num = input(DATE_ADDED,anydtdtm.);
put dt_num= datetime18.;
format dt_num datetime18.;
run;

However the output

for  column dt_num  is now a numeric Type, not a date type  and also is wrongly converting the days in months and keeping november (11) as day, so completely wrong . I need type as a date as I will do some date calculations later.

dt_num

Numeric

8

DATETIME18.

OUTPUT    FROM MY CODE                        

11JAN21:00:21:00

11JAN21:01:14:00

11FEB21:16:02:00

OUTPUT   EXPECTED                        

01NOV21:00:21:00

01NOV21:01:14:00

02NOV21:16:02:00

Many thanks in advance,

Clau

02NOV21:16:02:00

[PaigeMiller]

There is no such thing as a date type in SAS. A variable can either be character or numeric; no third possibility. Dates in SAS must be numeric, and then you can do calculations on them.

You will probably have to read in the date part of the character string separately, so you can use the MMDDYY. or DDMMYY. informat as needed. Then read in the time part using the TIME. informat. Lastly, if you want, you can combine the date part and the time part into a Date/Time variable.

[Reeza]

data work.cla;
set work.O2_Student_B;
date_num = input(scan(DATE_ADDED, 1),mmddyy.);
time_num = input(scan(date_added, 2), hhmm.);
dt_num = dhms(date_num, time_num, 0, 0);
put dt_num= datetime18.;
format dt_num datetime18. date_num date9 time_num time8.;
run;

---

@jorquec wrote:

Hi 

Please could someone help on this issue?

My first column despite being visualized as date ,SAS type is pointing as Char so I would like to format this Char type into a date type in this same format like ddmmyyyy hh:mm.

All my information on DATE_ADDED column is from November 2021, just the day and hour changes .

Examples

DATE_ADDED

01/11/2021 00:21

Char

16

02/11/2021 13:41

03/11/2021 13:31

I've tried

data work.cla;
set work.O2_Student_B;
dt_num = input(DATE_ADDED,anydtdtm.);
put dt_num= datetime18.;
format dt_num datetime18.;
run;

However the output

for  column dt_num  is now a numeric Type, not a date type  and also is wrongly converting the days in months and keeping november (11) as day, so completely wrong . I need type as a date as I will do some date calculations later.

dt_num

Numeric

8

DATETIME18.

OUTPUT    FROM MY CODE                        

11JAN21:00:21:00

11JAN21:01:14:00

11FEB21:16:02:00

OUTPUT   EXPECTED                        

01NOV21:00:21:00

01NOV21:01:14:00

02NOV21:16:02:00

Many thanks in advance,

Clau

02NOV21:16:02:00

---

[ballardw]

National language settings determine how SAS will interpret character values for use in Input function or informat treatment when the dates are ambiguous such as 01/11/2021 and the Informat used is one of the ANYDate versions. The default is apparently treating your data as MMDDYY so you need to take control and use a DDMMYY informat where needed. Perhaps:

data example;
   x="01/11/2021 00:21";
   dt =dhms( input(scan(x,1,' '),ddmmyy10.),0,0,input(scan(x,2,' '),time.));
   format dt datetime20.;
run;

the DHMS function takes date and time components to make a datetime value. The above code parses the date and time separately because of that layout. I strongly recommend displaying years in 4 digits by default.

Note: discussing these values in SAS Dates and Datetimes are not the same. They use different units of measures and referring to an actual Datetime value as a Date can yield incorrect responses to questions.

---

@jorquec wrote:

Hi 

Please could someone help on this issue?

My first column despite being visualized as date ,SAS type is pointing as Char so I would like to format this Char type into a date type in this same format like ddmmyyyy hh:mm.

All my information on DATE_ADDED column is from November 2021, just the day and hour changes .

Examples

DATE_ADDED

01/11/2021 00:21

Char

16

02/11/2021 13:41

03/11/2021 13:31

I've tried

data work.cla;
set work.O2_Student_B;
dt_num = input(DATE_ADDED,anydtdtm.);
put dt_num= datetime18.;
format dt_num datetime18.;
run;

However the output

for  column dt_num  is now a numeric Type, not a date type  and also is wrongly converting the days in months and keeping november (11) as day, so completely wrong . I need type as a date as I will do some date calculations later.

dt_num

Numeric

8

DATETIME18.

OUTPUT    FROM MY CODE                        

11JAN21:00:21:00

11JAN21:01:14:00

11FEB21:16:02:00

OUTPUT   EXPECTED                        

01NOV21:00:21:00

01NOV21:01:14:00

02NOV21:16:02:00

Many thanks in advance,

Clau

02NOV21:16:02:00

---

[AMSAS]

I recommend you go read Working with Dates in the SAS System
SAS does not have a date type, dates, times and datetimes are stored as numeric values

Next go look at the informat ANYDTDTM that you are using, when you check the Details section you will see it does not read in data that is in the format DD/MM/YYYY HH:MM, which appears to be the format of your data.

I think what is happening in your specific case is, ANYDTDTM is using the MDYAMPM informat to read your character variable "date_added"

What you are going to need to do, is take DATE_ADDED and manipulate it so it's in a format that can be converted into a SAS date/time value

data have ;
	input 
		   date_added  $16. 
  		+1 date_added2 $16. ;
cards ;
01/11/2021 00:21 11/01/2021 00:21
03/11/2021 13:31 11/03/2021 13:31
;

data want ;
	set have ;
	dt_num  = input(DATE_ADDED,anydtdtm.);
	dt_num1 = input(DATE_ADDED2,mdyampm.);
	put date_added= date_added2= ;
	put dt_num= datetime18. dt_num1= datetime18. ;
	format dt_num datetime18.;
run;

[Tom · Accepted solution]

You need to tell ANYDTDTM that you want it force ambiguous strings to be interpreted as DMY instead of MDY by setting the DATESTYLE option.

Because of bug in the DATETIME format you need increase the width by one extra character to force it to display four digit years.

data have ;
	input date_added $16. ;
cards ;
01/11/2021 00:21
03/11/2021 13:31
;

%let optsave=%sysfunc(getoption(datestyle,keyword));
%put &=optsave;

options datestyle=dmy;
data want;
  set have;
  datetime_added=input(date_added,anydtdtm16.);
  format datetime_added datetime19.;
run;
options &optsave;

proc print;
run;

Also what you have are DATETIME values (sometimes called TIMESTAMPs) not DATE values. DATE values in SAS are stored as number of days. DATETIME values SAS will store number of seconds. So you need to be clear about what you have to make sure you use the right functions and formats with the values.

[jorquec]

Many thanks for your brilliant solution.
It was really helpful.
Thanks.