topic Re: Formatting only date fields in excel worksheet during SAS dataset conversion in SAS Programming

Formatting only date fields in excel worksheet during SAS dataset conversion

jayakumarmm — Mon, 12 Sep 2016 19:22:19 GMT

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;

Re: Formatting only date fields in excel worksheet during SAS dataset conversion

PGStats — Mon, 12 Sep 2016 19:46:03 GMT

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;

Re: Formatting only date fields in excel worksheet during SAS dataset conversion

jayakumarmm — Mon, 12 Sep 2016 20:00:50 GMT

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.

Re: Formatting only date fields in excel worksheet during SAS dataset conversion

PGStats — Mon, 12 Sep 2016 21:11:45 GMT

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.

Re: Formatting only date fields in excel worksheet during SAS dataset conversion

jayakumarmm — Tue, 13 Sep 2016 00:53:09 GMT

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

Re: Formatting only date fields in excel worksheet during SAS dataset conversion

PGStats — Tue, 13 Sep 2016 02:32:07 GMT

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;

Re: Formatting only date fields in excel worksheet during SAS dataset conversion

jayakumarmm — Tue, 13 Sep 2016 03:02:28 GMT

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

Re: Formatting only date fields in excel worksheet during SAS dataset conversion

PGStats — Tue, 13 Sep 2016 03:23:21 GMT

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.

Re: Formatting only date fields in excel worksheet during SAS dataset conversion

jayakumarmm — Tue, 13 Sep 2016 11:50:12 GMT

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;