topic Re: Import an Excel file containing dates in SAS Programming

Import an Excel file containing dates

Gick — Thu, 26 Jan 2023 11:54:56 GMT

Hello,

I have a database in Excel with a date variable of modalities 01/21/2022, 03/02/2019, 06/01/2010, etc. When I import it into SAS, the date variable contains the modalities 44949. I tried this code to keep date variable as character in order to convert it, it doesn't work. My problem is with the import. Can anyone help me please.

Any suggestions for improving codes or another alternative are welcome.

Thanks in advance.

Gick

proc import datafile="I:\DOC\Questionnaires.xlsx" 
           dbms=xlsx 		   
           out=data replace ;
	mixed=yes;
     	   scantext=yes;
run;

Re: Import an Excel file containing dates

PaigeMiller — Thu, 26 Jan 2023 11:54:45 GMT

A quick search of the forum for "Excel dates" turns up plenty of correct answers

https://communities.sas.com/t5/forums/searchpage/tab/message?q=excel%20dates

Re: Import an Excel file containing dates

Kurt_Bremser — Fri, 27 Jan 2023 07:50:35 GMT

Save the sheet to a csv file and read that with a DATA step, where you have full control and can deal with special values which cause the IMPORT problem (reading dates as character).

The number you got is the internal representation of a date in Excel. Add '30dec1899'd to it to get the correct SAS date.

Re: Import an Excel file containing dates

Gick — Fri, 27 Jan 2023 08:00:18 GMT

Hello,

where do I add it?
Can you elaborate more please? For example by giving an example of code.

Thanks
Gick

Re: Import an Excel file containing dates

Gick — Fri, 27 Jan 2023 08:01:02 GMT

Hello,

where do I add it ? Can you elaborate more please? For example by giving an example of code.

Thanks.

Gick

Re: Import an Excel file containing dates

Oligolas — Fri, 27 Jan 2023 08:05:07 GMT

Hi,

common source of issue when data providers do not use a suitable database system.

Either you proceed as @Kurt_Bremser said, converting to *.csv where format are kept

or you import as you did and perform the correct date conversion afterwards.

Excel do not use the same start time reference value as SAS, so you have to convert by yourself.

YOUR_DTC=put(input(XLSX_COLUMN_NAME,best32.)-21916,e8601da.);

so you get "2023-01-23"

Re: Import an Excel file containing dates

Gick — Fri, 27 Jan 2023 08:27:32 GMT

it does not work. Here is the message I get in the log

ERROR: Variable date has been defined as both character and numeric.

Re: Import an Excel file containing dates

Kurt_Bremser — Fri, 27 Jan 2023 10:46:00 GMT

In order to supply working code, we need usable example data so we know the names and attributes of your variables. Post example data as a working DATA step with DATALINES, so we can easily replicate your data in our environments for testing.

The ERROR indicates that you either try to convert "in place", which is not possible, or you already have a variable called date with the wrong type.

In the future, whenever you get WARNINGs or ERRORs, or have other issues with your code, post the complete (all code and all messages) log by copy/pasting into a window opened with this button:

Re: Import an Excel file containing dates

PaigeMiller — Fri, 27 Jan 2023 13:11:41 GMT

@Gick wrote:
Hello,

where do I add it?
Can you elaborate more please? For example by giving an example of code.

Thanks
Gick

The link I gave has oodles of examples of actual SAS code to do this.

Re: Import an Excel file containing dates

Oligolas — Fri, 27 Jan 2023 13:28:07 GMT

if you already have a numeric value use

YOUR_DTC=put(XLSX_COLUMN_NAME-21916,e8601da.);

Difficult to say without knowing your data

Re: Import an Excel file containing dates

AMSAS — Fri, 27 Jan 2023 14:24:51 GMT

Find below a simple example using the Excel date Values you had in the initial post

A couple of things to note:

Excel stores dates as a number e.g. 1 = 01 Jan 1990, 2 = 02 Jan 1990, ...
Excel doesn't appear to handle dates prior to 1900 (I'm no Excel expert, just an observation when looking into this post)
SAS stores dates as the number of days since 01 Jan 1960 e.g. 0 = 01 Jan 1960, 1 = 02 Jan 1960, ...

Once you know this it's a relatively simple task to convert Excel date values to SAS date values, you just need to know the number of days to subtract from the Excel date value to convert it to a SAS Date Value. Knowing that 01 Jan 1960 is represented as 21,916 in Excel and 0 in SAS tells us to subtract 21,916 from the Excel Date Value.

/* Simulated date values from Excel */
data have ;
	infile cards ;
	input excelDateValue ;
cards ;
44582
43526
40330
21916
1
;

/* Read the simulated date values and conver to SAS Date values */
data want ;
	format sasDateValue date7. ;
	set have ;
	sasDateValue=excelDateValue-21916 ;
	put sasDateValue= 8. sasDateValue= ;
run ;

Re: Import an Excel file containing dates

Tom — Fri, 27 Jan 2023 15:02:26 GMT

Do not use magic numbers in programming.

The number used in your code posted is 30DEC1899 .

   1  %put %sysfunc(putn(-21916,date9));
30DEC1899

So just use the actual date in the code:

sasDateValue=excelDateValue+'30DEC1899'd ;

Also you left out of your explanation for the reason why you want to use the 30th instead of the 31st. It is because Excel mimics the decision made for Lotus 1-2-3 to treat the year 1900 as a leap year which causes the difference to be off by one day (at least for dates from 01MAR1900 on).

Re: Import an Excel file containing dates

Kurt_Bremser — Fri, 27 Jan 2023 15:02:59 GMT

Excel does not work with dates prior to 1900 because it perpetuates a bug (actually, a trick to speed up calculations) it inherited from Lotus 1-2-3. It considers 1900 a leap year, which it was not. So all dates before March 1, 1900 are calculated wrongly.

This is also the reason why the correction value is 30dec1899 and not 31dec1899.

Other office software (e.g. LibreOffice) has this corrected and does therefore work with dates prior to 1900.

Re: Import an Excel file containing dates

SASKiwi — Fri, 27 Jan 2023 21:26:54 GMT

I suggest you try the EXCEL engine rather than XLSX:

proc import datafile="I:\DOC\Questionnaires.xlsx" 
           dbms=EXCEL		   
           out=data replace ;
	mixed=yes;
     	   scantext=yes;
run;

If that doesn't help then try saving the column type in Excel as "Date" or "Short Date" instead of the default "General". Then rerun the above code.

Re: Import an Excel file containing dates

Patrick — Sat, 28 Jan 2023 10:58:00 GMT

Excel is NOT a database and for this reason you can't rely on getting a consistent result when importing into SAS.

If you can at least rely on the structure of the Excel source - like in the first column should always be a string for a specific variable - then below an approach I'm using sometimes.

It's reading the Excel with the header row included as data so the resulting SAS variables always become type character and then mapping these initial columns to what I really need.

/* read Excel headers as data so all variables become character */
%let path=<some path>;
%let xlsx_file=<excel workbook name>;
proc import 
  file="&path/&xlsx_file"
  out=work._xl_src
  dbms=xlsx
  replace
  ;
  datarow=1;
  getnames=no;
run;

/* map initial variables to desired variable */
data work._dq_&p1._&target_tbl.;
  set work._xl_src(firstobs=2);
  drop a-<last column from Excel import>;

  /* map source to target columns */
  want_var_1            = input(A, ?? 32.   );
  want_var_2            = input(B, ?? $30.  );
  ...and so on...
run;