DATA Step, Macro, Functions and more

Formatting only date fields in excel worksheet during SAS dataset conversion

Accepted Solution Solved
Reply
Contributor
Posts: 55
Accepted Solution

Formatting only date fields in excel worksheet during SAS dataset conversion

[ Edited ]

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;

 


Accepted Solutions
Solution
‎09-13-2016 07:51 AM
Contributor
Posts: 55

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

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;

View solution in original post


All Replies
Respected Advisor
Posts: 4,925

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

Posted in reply to jayakumarmm

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;

 

PG
Contributor
Posts: 55

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

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.

 

 

Respected Advisor
Posts: 4,925

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

Posted in reply to jayakumarmm

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.

PG
Contributor
Posts: 55

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

[ Edited ]

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

Respected Advisor
Posts: 4,925

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

Posted in reply to jayakumarmm

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;
PG
Contributor
Posts: 55

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

Thanks a lot but actually I want to convert from 42521 to 05/31/2016
.i.e txt = "42521"
Respected Advisor
Posts: 4,925

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

Posted in reply to jayakumarmm

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.

PG
Solution
‎09-13-2016 07:51 AM
Contributor
Posts: 55

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

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 805 views
  • 0 likes
  • 2 in conversation