BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jayakumarmm
Quartz | Level 8

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
jayakumarmm
Quartz | Level 8

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

8 REPLIES 8
PGStats
Opal | Level 21

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
jayakumarmm
Quartz | Level 8

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
Opal | Level 21

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
jayakumarmm
Quartz | Level 8

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
Opal | Level 21

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
jayakumarmm
Quartz | Level 8
Thanks a lot but actually I want to convert from 42521 to 05/31/2016
.i.e txt = "42521"
PGStats
Opal | Level 21

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
jayakumarmm
Quartz | Level 8

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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