adding dynamic title parameters (without pronpt) in ‘List data task’ like Period From and To Date in Enterprise Guide.
I have to generate a report in EG in which we will have title specifying the dynamic date.
For example: the system should take the current system date and get previous month start date and end date.
I know intnx function does that work but how should i embed this function in that report title.
First, it may be useful to understand how INTNX works. If you run the following code in an EG code node:
date1 = today();
date2 = intnx('month',date1,1,'beginning');
date3 = intnx('month',date1,-1,'beginning');
date4 = intnx('month',date1,-1,'end');
proc print data=testit;
format date1 date2 date3 date4 mmddyy10.;
...and you run it in July, you will see that date2 is set to August 1, 2007 and date3 is set to June 1, 2007 and date4 is set to June 30, 2007.
So, now that INTNX is working the way we want, we can make some SAS macro variables from today's date using the INTNX function. In order to use the INTNX function, we need to use the %SYSFUNC macro function to make the macro variables. Again, if you submit this code in an EG code node, you will see that the title for the PROC PRINT has the correct dates and they are formatted with the mmddyy10. format.
** how it works in Macro '%LET' statement;
** CANNOT nest function calls for '%sysfunc';
** so you need to do this in 3 steps;
** first get the date, then use INTNX and then do the format;
title "Use Beginning: &fmtbeg and Ending: &fmtend Dates";
title2 "Alternative Beg: %sysfunc(putn(&prevbeg,mmddyy10.)) and End: %sysfunc(putn(&prevend,mmddyy10.))";
proc print data=sashelp.class;
So, now, you need to set up your LIST DATA task and put the Title ONLY (not the whole TITLE statement) in the TITLE area of the Task Title pane:
Use Beginning: &fmtbeg and Ending: &fmtend Dates
Alternative Beg: %sysfunc(putn(&prevbeg,mmddyy10.)) and End: %sysfunc(putn(&prevend,mmddyy10.))
(although, you would probably only choose one of these. The advantage of using &fmtbeg and &fmtend is that they are simple macro variable references. the advantage of using the second method is that you are not making an extra macro variable, but are instead doing the formatting of the date inside the title string.)
THEN, you have to PREVIEW the List Data code. When you do a preview of the code, there is a button that says "Insert Code" -- when you click that, it shows the places where you can insert code by showing you a grayish bar. Click on the first place and paste in these 5 lines:
Note that there are no quotes in the %SYSFUNC invocation of the INTNX function. This is because the macro facility only deals with macro variables or generated text as text strings, therefore, the usual quotes around 'month' or 'beginning' or 'end' are not needed in a %SYSFUNC call.
So, you are using either &fmtbeg or &fmtend in your title (or &prevbeg or &prevend) for the LIST DATA task. But for that macro variable reference to work, you need the %LET statements added to the LIST DATA code that EG generates -- using PREVIEW CODE and INSERT CODE.