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.
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
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,
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.
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.