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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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:

might_not_need_tagattr.png

 

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

View solution in original post

4 REPLIES 4
Reeza
Super User

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,

BCNAV
Quartz | Level 8

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.

Reeza
Super User

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.

 

Cynthia_sas
SAS Super FREQ

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:

might_not_need_tagattr.png

 

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 4 replies
  • 3434 views
  • 4 likes
  • 3 in conversation