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

I am attempting to output a date9. formatted date field in SAS to an Excel spreadsheet via ODS tagsets.excelxp and Proc Report. Once in Excel, I would like the data to be treated as a "date" type as opposed to "general" or "text", because I need to apply a date filter. The format of the dates in Excel needs to be m/d/yyyy.

 

The code is malfunctioning and shows the dates in Excel as 1/1/1960. Does anyone know how this code needs to be edited in order to meet the above requirements? Please help. Thanks.

 

 

data test;
input test_date date9.;
format test_date date9.;
datalines;
17FEB2019
18FEB2019
15JAN2019
16JAN2019
;
run;

proc print data=test; run;

ods tagsets.excelxp file="C:\Users\ABC123\Desktop\test.xls"
		style=sasdocPrinter 
		options(autofilter='all');


proc report data=test;
column test_date;
define test_date / 'test_date' center format=e8601dt. style(column) = [tagattr="type:DateTime format:m/d/yyyy"];
run;

ods tagsets.excelxp close;
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

E8601DT is a datetime format; because of the date and datetime math in SAS (counts of days vs counts of seconds) you'll get timestamps on 1960-01-01.

To ISO-format dates, use the E8601DA10. or YYMMDDD10. formats.

View solution in original post

1 REPLY 1
Kurt_Bremser
Super User

E8601DT is a datetime format; because of the date and datetime math in SAS (counts of days vs counts of seconds) you'll get timestamps on 1960-01-01.

To ISO-format dates, use the E8601DA10. or YYMMDDD10. formats.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 1 reply
  • 2115 views
  • 0 likes
  • 2 in conversation