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.
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.
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.
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 |
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.