Formatting only date fields in excel worksheet during SAS dataset conversion

[jayakumarmm]

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;

[PGStats]

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;

[jayakumarmm]

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.

[PGStats]

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.

[jayakumarmm]

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

[PGStats]

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;

[jayakumarmm]

Thanks a lot but actually I want to convert from 42521 to 05/31/2016
.i.e txt = "42521"

[PGStats]

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.

[jayakumarmm · Accepted solution]

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;