I need a help in formatting only the date fields in source while creating a SAS dataset. I have 90-100 fields in excel workbook, I want to format all the date fields to date format since date fields are getting converted to char/numeric formats
libname xlsFile XLSX "/usr/path.xlsm";
PROC SQL;
create table work.SASdataset1 as
(select * from excel_table);
run;
Thank you for your response. I am finally able to achieve this,
Date fields from excel need to be treated differently
Data work.dataset2;
set work.dataset1;
yd = date1 - 21916;
format yd mmddyy10.;
run;
You can use ALTER table in proc SQL, or a format statement within a data step:
PROC SQL;
create table SASdataset1 as select * from excel_table;
alter table SASdataset1 modify date1 format=yymmdd10., date2 format=yymmdd10.;
quit;
/* Or */
DATA SASdataset1;
set excel_table;
format date1 date2 yymmdd10.;
run;
Thank you so much for your response. I am getting a below error after implementing Option 1 from your response.
ERROR: Character column Date1 requires a character format specification.
ERROR: Character column Date2 requires a character format specification.
A data step might be simpler then
DATA SASdataset1;
set excel_table;
d1 = input(date1, anydtdte.);
d2 = input(date2, anydtdte.);
format d1 d2 yymmdd10.;
drop date1 date2;
run;
if required, replace informat anytdte. with whatever format your character date fields are in.
Thanks for your reply. I am still finding difficulty in converting the date fields back to any proper date format in SAS dataset.
Actual scenario - I need to use the same converion approach for all three source fields since any future dataset may contain all date fields in characters again. Please assist.
source date1 SAS_dataset
5/31/2016 42521
source date2 SAS_dataset
5/6/2016 42496
Source date3 SAS_dataset
05/23/2016 05/23/2016
The default length for informat anydtdte. is 9 which is not enough to read "05/23/2016". Use anydtdte32. instead
data _null_;
txt = "05/23/2016";
d = input(txt, anydtdte32.);
put txt= d= yymmdd10.;
run;
You should get more familiar with the date concept in SAS. A SAS date is a number (e.g. the integer 42521, not a character string) which can be represented in print in many ways, depending on the specified format.
The particular format that you want is mmddyy10.
Thank you for your response. I am finally able to achieve this,
Date fields from excel need to be treated differently
Data work.dataset2;
set work.dataset1;
yd = date1 - 21916;
format yd mmddyy10.;
run;
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.