Date Format changes while importing into sas from excel

[TB10]

In my excel file i have a column as 'Start Date' (with space in between). and the date format is mm/dd/yyyy hh:mm format.

While i am importing the file in sas the format is changing to ddmmmyyyy (eg. 02JUN2023).

How do i retain the original format?

Code:

%let filepath='/user/drive1/file/';

proc import datafile="&filepath.test.xlsx";

out=work.rawdata

dbms=excelcs 

replace;

run;

@Tom @fja 

[Tom]

I don't know about EXCELCS, but if you use XLSX then it will properly import a DATETIME value as a DATETIME value into SAS.

So if you make a XLSX file with a cell that uses the NOW() function to insert a datetime value, like this:

And import it into SAS using PROC IMPORT, like this:

proc import file='c:\downloads\datetime.xlsx' dbms=xlsx out=want replace;
run;

You will get a NUMERIC variable with DATETIME values.  By default it will attach the DATETIME format to the variable so humans can recognize the value as a datetime value.

Obs          Start Date

 1     17JUL23:15:44:21

If you want the values to print in a different way then attach a different FORMAT to the variable.  SAS has many format than can display DATETIME values.

1097  data datetime;
1098    set sashelp.vformat;
1099    where 'datetime'=fmtinfo(fmtname,'cat')
1100      and fmttype='F'
1101    ;
1102  run;

NOTE: There were 75 observations read from the data set SASHELP.VFORMAT.
      WHERE ('datetime'=FMTINFO(fmtname, 'cat')) and (fmttype='F');
NOTE: The data set WORK.DATETIME has 75 observations and 13 variables.
NOTE: DATA statement used (Total process time):
      real time           0.11 seconds
      cpu time            0.12 seconds

You can try seeing if any of them will print in that confusing MDY order.

data datetime;
  if _n_=1 then set want ;
  set sashelp.vformat;
  where 'datetime'=fmtinfo(fmtname,'cat')
    and fmttype='F'
  ;
  length formatted $200;
  formatted = putn('start date'n,cats(fmtname,maxw,'.-L'));
  format 'start date'n comma24. ;
  keep 'start date'n fmtname maxw formatted ;
run;

proc print width=min; run;

Spoiler

Obs     Start Date      fmtname      maxw    formatted

  1    2,005,227,861    AFRDFDT        40    17Jul2023:15:44:21
  2    2,005,227,861    B8601DN        10    20230717
  3    2,005,227,861    B8601DT        26    20230717T154421
  4    2,005,227,861    B8601DX        35    20230717T114421-0400
  5    2,005,227,861    B8601DZ        35    20230717T154421+0000
  6    2,005,227,861    CATDFDT        40    17Jul2023:15:44:21
  7    2,005,227,861    CRODFDT        40    17srp2023:15:44:21
  8    2,005,227,861    CSYDFDT        40    17?ervenec2023:15:44:21
  9    2,005,227,861    DANDFDT        40    17jul2023:15:44:21
 10    2,005,227,861    DATEAMPM       40    17JUL2023:03:44:21 PM
 11    2,005,227,861    DATETIME       40    17JUL2023:15:44:21
 12    2,005,227,861    DESDFDT        40    17Jul2023:15:44:21
 13    2,005,227,861    DEUDFDT        40    17Jul2023:15:44:21
 14    2,005,227,861    DTDATE          9    17JUL2023
 15    2,005,227,861    DTMONYY         7    JUL2023
 16    2,005,227,861    DTWKDATX       37    Monday, 17 July 2023
 17    2,005,227,861    DTYEAR          4    2023
 18    2,005,227,861    DTYYQC          6    2023:3
 19    2,005,227,861    E8601DN        10    2023-07-17
 20    2,005,227,861    E8601DT        26    2023-07-17T15:44:21
 21    2,005,227,861    E8601DX        35    2023-07-17T11:44:21-04:00
 22    2,005,227,861    E8601DZ        35    2023-07-17T15:44:21+00:00
 23    2,005,227,861    ENGDFDT        40    17JUL2023:15:44:21
 24    2,005,227,861    ESPDFDT        40    17jul2023:15:44:21
 25    2,005,227,861    EURDFDT        40    17JUL2023:15:44:21
 26    2,005,227,861    FINDFDT        40    17.7.2023:15:44:21
 27    2,005,227,861    FRADFDT        40    17jul2023:15:44:21
 28    2,005,227,861    FRSDFDT        40    17jul2023:15:44:21
 29    2,005,227,861    HUNDFDT        40    23.júl.17. 15:44:21
 30    2,005,227,861    IS8601DN       10    2023-07-17
 31    2,005,227,861    IS8601DT       26    2023-07-17T15:44:21
 32    2,005,227,861    IS8601DZ       35    2023-07-17T15:44:21+00:00
 33    2,005,227,861    ITADFDT        40    17Lug2023:15:44:21
 34    2,005,227,861    JDATEYT       200
 35    2,005,227,861    JDATEYTW      200
 36    2,005,227,861    JNENGOT       200
 37    2,005,227,861    JNENGOTW      200
 38    2,005,227,861    MACDFDT        40    17jul2023:15:44:21
 39    2,005,227,861    MDYAMPM        40    7/17/2023  3:44 PM
 40    2,005,227,861    NLDATM        200    17Jul2023:15:44:21
 41    2,005,227,861    NLDATMAP      200    July 17, 2023 03:44:21 PM
 42    2,005,227,861    NLDATMDT      200    July 17, 2023
 43    2,005,227,861    NLDATML       200    July 17, 2023 03:44:21 PM
 44    2,005,227,861    NLDATMM       200    Jul 17, 2023 03:44:21 PM
 45    2,005,227,861    NLDATMMD      200    July 17
 46    2,005,227,861    NLDATMMDL     200    July 17
 47    2,005,227,861    NLDATMMDM     200    Jul 17
 48    2,005,227,861    NLDATMMDS     200    07/17
 49    2,005,227,861    NLDATMMN      200    July
 50    2,005,227,861    NLDATMS       200    07/17/2023 15:44:21
 51    2,005,227,861    NLDATMTM      200    15:44:21
 52    2,005,227,861    NLDATMTZ      200    15:44:21 -0400
 53    2,005,227,861    NLDATMW       200    Monday, July 17, 2023 03:44:21 PM
 54    2,005,227,861    NLDATMWN      200    Monday
 55    2,005,227,861    NLDATMWZ      200    Monday, July 17, 2023 03:44:21 PM -0400
 56    2,005,227,861    NLDATMYM      200    July 2023

The SAS System                                                                                       15:26 Sunday, July 16, 2023  57

Obs     Start Date      fmtname      maxw    formatted

 57    2,005,227,861    NLDATMYML     200    July 2023
 58    2,005,227,861    NLDATMYMM     200    Jul 2023
 59    2,005,227,861    NLDATMYMS     200    07/2023
 60    2,005,227,861    NLDATMYQ      200    3rd quarter 2023
 61    2,005,227,861    NLDATMYQL     200    3rd quarter 2023
 62    2,005,227,861    NLDATMYQM     200    Q3 2023
 63    2,005,227,861    NLDATMYQS     200    2023/3
 64    2,005,227,861    NLDATMYR      200    2023
 65    2,005,227,861    NLDATMYW      200    Week 29 2023
 66    2,005,227,861    NLDATMZ       200    17Jul2023:15:44:21 -0400
 67    2,005,227,861    NLDDFDT        40    17jul2023:15:44:21
 68    2,005,227,861    NORDFDT        40    17jul2023:15:44:21
 69    2,005,227,861    POLDFDT        40    17lip2023:15:44:21
 70    2,005,227,861    PTGDFDT        40    17jul2023:15:44:21
 71    2,005,227,861    RUSDFDT        40    17???2023:15:44:21
 72    2,005,227,861    SLODFDT        40    17jul2023:15:44:21
 73    2,005,227,861    SVEDFDT        40    17jul2023:15:44:21
 74    2,005,227,861    TWMDY          35    15:44 Monday, July 17, 2023
 75    2,005,227,861    UNXEPOCH       32    1689608661

Looks like NLDATMS will print in that MDY HMS order.

1231  data _null_;
1232   now=datetime();
1233   put now=datetime19. now NLDATMS16. ;
1234  run;

now=17JUL2023:16:21:49 07/17/23 16:21

But I don't see one that does the equally confusing DMY HMS ordering.

If what you want is not there you create your own custom format using the PICTURE statement of PROC FORMAT.

[TB10]

Thanks for your reply @Tom and @fja   however i am importing the file from windows shared drive and not from sas grid. Hence xlsx is not  working. I am trying to upload the file from shared drive but still it's not working.(please note port and connectivity is open between sas grid and windows share drive and proc upload through RUWIN is working vice versa when i am trying to copy the file from sas grid to shared drive).

[fja]

Hmm ... but you have manged to import the file? Is that correct? And the datatype of the column in question is "datetime" in the resulting dataset?
--fja

[TB10]

Yes that's the correct understanding. I have managed to import the file through excelcs but date column format is changing.

[fja]

... could you verify the type of the column/field in question, pls?

[TB10]

The type is Date

[Tom]

---

@TB10 wrote:
The type is Date

---

Not in SAS.

SAS datasets only have two types of variable.  Fixed length character strings and floating point numbers.

DATE, TIME and DATETIME values are stored as NUMBERS.  DATE values are stored as number of days since start of 1960.  TIME and DATETIME values are stored as number of seconds.  TIME values as number of seconds from midnight. DATETIME values as number of seconds since start of 1960.

To have the value PRINT in a way that humans can recognize as a DATE, TIME or DATETIME value you have to attach a format to the variable.

So if IMPORT with the EXCELCS engine is causing SAS to convert a DATETIME value in the XLSX file into a DATE value in SAS there is something wrong.   Check the actual values in the variable by printing it without any format or a simple one like the BEST format.

Note that in XLSX files DATE and DATETIME values are both stored as number of days.  The time of day part is stored as a fraction of a day.   If the actual values in your SAS variable are not integers then the time part was imported, but just not converted to a datetime value properly.  You can make a DATETIME value from a DATE value by using the DHMS() function.

datetime = dhms(date,0,0,0);

If the DAY value has fractions of a day included those will be converted to the proper number of seconds.

If PROC IMPORT is in fact converting a DATETIME value into a DATE value then perhaps you can try using EXELCS engine in a LIBNAME statement instead of using PROC IMPORT.  If it works the same as the EXCEL engine then it should allow you to use the DBSASTYPE dataset option to force SAS to convert the variable to DATETIME values.

[fja]

---

@TB10 wrote:
The type is Date

---

I fear that I cannot help in this case. I know too little of pc file servers and excelcs. As Tom has already mentioned there seems to be something severely wrong as part of the information (time) is cut out ... so it is - by the looks of it - not a matter of format but a matter of transforming information (reading xl files). May I suggest to have a word with your administrator?

-fja

[fja]

---

@TB10 wrote:

In my excel file i have a column as 'Start Date' (with space in between). and the date format is mm/dd/yyyy hh:mm format.

While i am importing the file in sas the format is changing to ddmmmyyyy (eg. 02JUN2023).

How do i retain the original format?

---

Hello!

The issue has been covered by @Tom 's posting thoroughly. You should get along with his remarks perfectly. I guess NLDATTML19.0 would be close enough.

I do not know how deep you are in SAS. Maybe an explainatory word: There is no way of retaining a format of input data ... SAS reads data and stores it in a way you tell it ... you can only make it output the data again in a given format. And this is what's going on ... SAS reads your data and outputs in the standard format again as it is not told to do otherwise. And there Tom's posting hocks in ... 

Cheers

fja

[fja]

Last try! 🙂

Have you had a look at https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acpcref/n0msy4hy1so0ren1acm90iijxn8j.htm#n1t0...

? It seems, that there are two options, SCANTIME and USEDATE that influence the format that is applied to date/time data ... I guess (!) USEDATE=NO and SCANTIME = NO does the trick. 

Not sure (and entirely untested) ... I'd try all four possibilities. 

Please report any progress ... in case you are successful

--fja