topic Re: ODS Export Date to Excel Properly in SAS Programming

ODS Export Date to Excel Properly

BCNAV — Thu, 31 Aug 2017 16:44:10 GMT

This is probably simple, but I'm stuck I have, as part of a data set the following:

data gvars;
	%global leadvar sas_date sas_date_fs sas_date_fe conf_int data_source f_var yaxisformat;

	/* Data location library */
	call symput('data_source','NAVTS.NAV_DATA_ALL_TS');


	/* User entries below */
	sas_date_endhist='31Jul2017'd;
	sas_date_fcst_start='1Aug2017'd;
	sas_date_fcst_end='31Aug2018'd;
	conf_int_tmp=0.05;

run;

What I need to do is to print the variable sas_date_endhist to an Excel file (preferably with ODS Excel) and have SAS ensure Excel will read the date; Excel will use that date later. I would like only the month and year to be output from SAS.

Thanks in advance.

Re: ODS Export Date to Excel Properly

Reeza — Thu, 31 Aug 2017 16:48:53 GMT

There's no ODS code there...

What version of SAS do you have? Before 9.4 TS1M3 ODS EXCEL was pre-production and had some issues,

Re: ODS Export Date to Excel Properly

BCNAV — Thu, 31 Aug 2017 16:56:06 GMT

Bah...sorry

We have:

9.4 TS Level 1M3

EG 7.12 (7.100.2.3350) (64-bit)

ODS I had tried is:

ods excel file="R:\Traffic Forecasting\TFM 3.0\Hist Date.xlsx";
ods excel options(start_at="1,1" sheet_name="Date" sheet_interval="table" embedded_titles='no');
proc print data=gvars noobs;
var sas_date_endhist;
run;
ods excel close;

But again I am not sure how to output just the month and year to a fromat Excel will honor.

Re: ODS Export Date to Excel Properly

Reeza — Thu, 31 Aug 2017 17:14:01 GMT

Find the correct excel format and apply it with a TAGATTR style.

Here's a paper on how that works - it's designed for ODS TAGSETS but pretty sure it works in ODS EXCEL.

Re: ODS Export Date to Excel Properly

Cynthia_sas — Thu, 31 Aug 2017 20:26:34 GMT

Hi:

With ODS Excel and most regular SAS date formats, I find that I do NOT need TAGATTR. Here's an example:

data testdate;
  infile datalines;
  input name $ date : mmddyy.;
  format date mmddyy10.;
return;
datalines;
alan 01/01/1960
barb 09/29/1950
carl 08/31/2017
;
run;

ods excel file='c:\temp\testdate.xlsx'
    options(sheet_interval='none' embedded_titles='yes');

title 'ODS EXCEL -- if use SAS format, TAGATTR might not be needed';
proc report data=testdate;
  compute before _page_ / style=Header;
    line '1) date is formatted mmddyy10.';
  endcomp;
run;
title;

proc report data=testdate;
  format date date9.;
  compute before _page_ / style=Header;
    line '2) use different format';
  endcomp;
run;


proc report data=testdate;
  format date worddate.;
  compute before _page_ / style=Header;
    line '3) use worddate format';
  endcomp;
run;
ods excel close;

...and the output:

I love that my SAS Format is respected by Excel when I use ODS EXCEL - -I always have TAGATTR for custom dates, but this is a cool feature.

cynthia