topic Re: How to convert a char variable to date in Forum em português

How to convert a char variable to date

Vigo — Tue, 15 Mar 2022 20:03:14 GMT

Hello everyone,

Hope to find you all well. I have a .xlsx data file and using 'proc import' they are read in char format. The data in excel sheet are as below:

id data_upload_elsa calendar_date
1 2017-05-04T18:15:47.717Z 2017-04-19T20:00:00-0300
2 2017-05-04T18:15:47.717Z 2017-04-20T20:00:00-0300
3 2017-05-04T18:15:47.717Z 2017-04-21T20:00:00-0300
4 2017-05-04T18:15:47.717Z 2017-04-22T20:00:00-0300
5 2017-05-04T18:15:47.717Z 2017-04-23T20:00:00-0300
6 2017-05-04T18:15:47.717Z 2017-04-24T20:00:00-0300
7 2017-05-04T18:15:47.717Z 2017-04-25T19:59:55-0300
8 2017-05-03T18:51:27.674Z 2017-04-20T20:00:00-0300
9 2017-05-03T18:51:27.674Z 2017-04-21T20:00:00-0300
10 2017-05-03T18:51:27.674Z 2017-04-22T20:00:00-0300

I've already tried importing using the statement

data_upload = input(data_upload_elsa,datetime.);

but without success. I would be very grateful if anyone can suggest a way to convert these variables to the 'datetime' format.

Thank you all in advance.

Best regards,

Alvaro

data_upload = input(data_upload_elsa,datetime.);

Re: How to convert a char variable to date

yabwon — Tue, 15 Mar 2022 20:13:54 GMT

You have ISO8601 date so use proper informat:

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/leforinforref/p1a0qt18rxydrkn1b0rtdfh2t8zs.htm#p02ptihnnczk6qn1fdh9qjqin0sp

Bart

Re: How to convert a char variable to date

Reeza — Tue, 15 Mar 2022 22:09:01 GMT

Your code is mostly correct, you need to use a different informat.

@yabwon has pointed you to the correct informat but linked you to the format not informat section:
https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/leforinforref/p1a0qt18rxydrkn1b0rtdfh2t8zs.htm#p02ptihnnczk6qn1fdh9qjqin0sp

You can also navigate the informats directly here and I think this is the format for both your variables
https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/leforinforref/p14nbxgmr6gn7tn1ntmuulgawrih.htm

Re: How to convert a char variable to date

Tom — Tue, 15 Mar 2022 22:38:01 GMT

That second one is not valid style for the SAS informat because there is no colon in the offset time.

You can stick in the colon and then the informat will work.

Example:


data have;
  input id (data_upload_elsa calendar_date) (:$40.);
  dt1=input(data_upload_elsa,e8601dz25.);
  dt2=input(cats(substr(calendar_date,1,22),':',substr(calendar_date,23)),e8601dz25.);
  format dt: datetime24.3;
cards;
1 2017-05-04T18:15:47.717Z 2017-04-19T20:00:00-0300
2 2017-05-04T18:15:47.717Z 2017-04-20T20:00:00-0300
3 2017-05-04T18:15:47.717Z 2017-04-21T20:00:00-0300
4 2017-05-04T18:15:47.717Z 2017-04-22T20:00:00-0300
5 2017-05-04T18:15:47.717Z 2017-04-23T20:00:00-0300
6 2017-05-04T18:15:47.717Z 2017-04-24T20:00:00-0300
7 2017-05-04T18:15:47.717Z 2017-04-25T19:59:55-0300
8 2017-05-03T18:51:27.674Z 2017-04-20T20:00:00-0300
9 2017-05-03T18:51:27.674Z 2017-04-21T20:00:00-0300
10 2017-05-03T18:51:27.674Z 2017-04-22T20:00:00-0300
;

proc print;
run;

Re: How to convert a char variable to date

Vigo — Tue, 22 Mar 2022 12:59:11 GMT

Hello Tom,
Thank you very much for your time and willingness to help with this issue. I really appreciated your contribution.
Regards,

Re: How to convert a char variable to date

Vigo — Tue, 22 Mar 2022 13:01:38 GMT

Thank you all for your time and willingness to help with this issue. I really appreciated your contribution.

Regards,