topic Re: SAS dates won't import from Excel in SAS Programming

SAS dates won't import from Excel

wml_3 — Thu, 15 Oct 2020 16:48:50 GMT

I have been using the same code and data source to run weekly reports without any issues using, however, this week one of my date columns will not import from Excel. The variable 'collectiondate' is formatted as Date in Excel but when it imports to SAS the entire column of data is missing and the informat and format of the column are TIME8. instead of the preferred DATE9. format that I have been using. It shows up the same way in all four of the data sets created. Any idea why this would be happening all of a sudden and ideas on how to fix it? I had been using 9.3 but recently switched to 9.4 if that makes any difference. Screenshots and code below.

OPTIONS MPRINT;

%MACRO IMP(INSHEET=);
PROC IMPORT OUT=WORK.&INSHEET
      DATAFILE="filepathway...\NHSN_LTCF_covid19 &extract_date..xlsx"
      DBMS=EXCEL REPLACE;
      SHEET="&INSHEET.$";
      GETNAMES=YES;
      MIXED=NO;
      SCANTEXT=YES;
      USEDATE=YES;
      SCANTIME=YES;
RUN;

%MEND IMP;

%IMP(INSHEET=covid19res);
%IMP(INSHEET=covid19staff);
%IMP(INSHEET=covid19supp);
%IMP(INSHEET=covid19vent);

Re: SAS dates won't import from Excel

Reeza — Thu, 15 Oct 2020 17:00:39 GMT

Try using XLSX instead to import your data but using Excel means you have limitations on the control of the type of data you're importing. In the long it would be best to change your data format to a CSV.

PROC IMPORT OUT=WORK.&INSHEET
DATAFILE="filepathway...\NHSN_LTCF_covid19 &extract_date..xlsx"
DBMS=XLSX REPLACE;
SHEET="&INSHEET.$";
GETNAMES=YES;

RUN;

Re: SAS dates won't import from Excel

wml_3 — Thu, 15 Oct 2020 17:15:20 GMT

This solved the problem on the data not entering - thanks! Had to take a few more steps to get it into the correct format but at least I can work with it now.