topic Importing Excel Date as number (YYYYMM) into SAS as Date. in SAS Programming

Importing Excel Date as number (YYYYMM) into SAS as Date.

AlexPanebianco — Wed, 16 Feb 2022 11:53:27 GMT

Hi all. I'm looking to move a current Excel process into SAS, for convenience, however, I have an issue stemming from the fact the base data I have to work with is currently only available to me in Excel and will require importing.

One of my columns in Excel is a "date" column, written as YYYYMM (so, October 2015 would be 201510). This is stored in Excel under General format and not as a date, so when I import the sheet to SAS, it doesn't recognise that 201510 is supposed to be October 2015.

I do not have access to the base data itself (this is provided by a separate team) and would like to avoid having to manually alter the column every time it's received, if at all possible.

Is there a way I can import this, so SAS recognises the column as a list of dates, or so SAS converts the string to a format it can recognise as a date?

Any help is greatly appreciated.

Re: Importing Excel Date as number (YYYYMM) into SAS as Date.

Kurt_Bremser — Wed, 16 Feb 2022 12:50:37 GMT

You only need a simple conversion step after import:

data have;
date = 201510;
run;

data want;
set have;
format date yymmn6.;
date = input(put(date,6.),yymmn6.);
run;

But you can read the date correctly if you save the spreadsheet to a csv file and read the column from that with the YYMMN6. informat.

Re: Importing Excel Date as number (YYYYMM) into SAS as Date.

SASJedi — Wed, 16 Feb 2022 12:59:27 GMT

You could also read this in with a DATA step. If the column is formatted General with only digits in it:

it's probably being read into SAS as a numeric, and something like this would do the trick:

libname xl xlsx "c:\temp\have.xlsx";
data want;
   set xl.have;
   date=mdy(mod(DateAsNumber,100),1,int(DateAsNumber/100));
   format date mmddyy10.;
run;

Result:

DateAsNumber	date
202201	01/01/2022
202202	02/01/2022
202203	03/01/2022
202204	04/01/2022
202205	05/01/2022
202206	06/01/2022
202207	07/01/2022
202208	08/01/2022
202209	09/01/2022
202210	10/01/2022
202211	11/01/2022
202212	12/01/2022

Re: Importing Excel Date as number (YYYYMM) into SAS as Date.

AlexPanebianco — Mon, 21 Feb 2022 18:55:21 GMT

Excellent, thanks for this (and apologies for my slow response). I'll give this a try.