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.);
You have ISO8601 date so use proper informat:
Bart
Thank you all for your time and willingness to help with this issue. I really appreciated your contribution.
Regards,
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.ht...
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.ht...
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;
Hello Tom,
Thank you very much for your time and willingness to help with this issue. I really appreciated your contribution.
Regards,
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!