topic Date Help in ODS Excel Title in SAS Programming

Date Help in ODS Excel Title

BobbyG0627 — Tue, 09 Aug 2022 21:46:56 GMT

I have searched all kinds of website for the answers and none of the examples I am finding work.

I hope someone has the answer....

I assign a date at the beginning of my program

%let FromDate=01JUN2022;
%let ToDate=31JUL2022;

I reference it all throughout the program with no issues.

When it comes time to produce the excel output though it only comes out in sas date format, I need it in mmddyy10. format

this is the code sample that produced the above...


ODS EXCEL FILE="&flname." OPTIONS(SHEET_NAME="My Sheet" EMBEDDED_TITLES="YES" FROZEN_HEADERS="3" AUTOFILTER="ALL" );
RUN;
TITLE j=l "My Report";
TITLE2 j=l "&FromDate - &ToDate.";;
RUN;

Re: Date Help in ODS Excel Title

ballardw — Tue, 09 Aug 2022 21:55:52 GMT

Macro variables, such as you create, are TEXT. As such the only "format" that applies is the $ format for literal text.

You would need to create a second macro variable for use in the Title.

If you don't want to manually create the other value here is one way using what your have entered

%let FromDate=01JUN2022;

%let Titlefrom = %sysfunc(putn("&fromdate."d,mmddyy10.));

You could place all of that %sysfunc code directly into the Title2 statement but it can make harder to read code. Plus if you end up wanting that value somewhere else you would have the same code in multiple places.

Re: Date Help in ODS Excel Title

Reeza — Tue, 09 Aug 2022 21:59:07 GMT

TITLE2 j=l "%sysfunc(inputn(&FromDate, date9.), mmddyy10.) - %sysfunc(inputn(&ToDate, date9.), mmddyy10.)";

Here's a great, but longer and in depth, reference for dates and times in SAS
https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/ta-p/424354

@BobbyG0627 wrote:

I have searched all kinds of website for the answers and none of the examples I am finding work.

I hope someone has the answer....

I assign a date at the beginning of my program
%let FromDate=01JUN2022;
%let ToDate=31JUL2022;
I reference it all throughout the program with no issues.

When it comes time to produce the excel output though it only comes out in sas date format, I need it in mmddyy10. format

this is the code sample that produced the above...
ODS EXCEL FILE="&flname." OPTIONS(SHEET_NAME="My Sheet" EMBEDDED_TITLES="YES" FROZEN_HEADERS="3" AUTOFILTER="ALL" );
RUN;
TITLE j=l "My Report";
TITLE2 j=l "&FromDate - &ToDate.";;
RUN;

Re: Date Help in ODS Excel Title

PaigeMiller — Tue, 09 Aug 2022 22:35:48 GMT

%let FromDate=01JUN2022;
%let ToDate=31JUL2022;

These are not in MMDDYY10. appearance. You have to change the appearance of this macro variable value in order to get MMDDYY10.

data _null_;
    call symputx('fromdate1',put("&fromdate"D,mmddyy10.));
run;

and then later use &FROMDATE1 in TITLE2. Of course, you would perform the same conversion on &TODATE.

Re: Date Help in ODS Excel Title

BobbyG0627 — Wed, 10 Aug 2022 11:32:57 GMT

Thank you!!! this was exactly what i needed, I have several outputs so this was exactly what i needed

thank you!