- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
hello, i was running a report monthly and it is moving to weekly. I am trying to solve for having the end date on this date parameter update automatically weekly (every Monday). the report I run Monday should be cova_evnt_efcv_dt between '01Jan2021'd and '25Apr2022'd
and the following Monday the dates whould be cova_evnt_efcv_dt between '01Jan2021'd and '02May2022'd
I want the end date to update automaically every Monday for a YTD reprot. Can someone provide guidance with this?
and cova_evnt_efcv_dt between '01Jan2021'd and '31Mar2022'd
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
At the start of your program
%let today=%sysfunc(today());
and then later
cova_evnt_efcv_dt between '01Jan2021'd and &today
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
At the start of your program
%let today=%sysfunc(today());
and then later
cova_evnt_efcv_dt between '01Jan2021'd and &today
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
If the execution is on Monday, how about simply using date function as today?
and cova_evnt_efcv_dt between '01Jan2021'd and date()
Or, if you want to run it as of Monday of this week, you can also do the following code.
data _null_;
/* monday in this week. If you change the 3rd. parameter from 0 to 1, you can specify next Monday */
monday= intnx( 'week', date(), 0, 'beginning')+1;
call symputx('end_date',monday);
run;
how to use it.
and cova_evnt_efcv_dt between '01Jan2021'd and &end_date
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@japelin wrote:
If the execution is on Monday, how about simply using date function as today?
and cova_evnt_efcv_dt between '01Jan2021'd and date()
I like your answer better than mine!
... unless you need today's date in titles, in which case you would need the macro variable approach (and you would have to apply a format to it if you were going to use it in the title)
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Are you always looking a year back?
data want;
cova_evnt_efcv_dt = '01JAN1960'd; output;
cova_evnt_efcv_dt = '01JAN2021'd; output;
cova_evnt_efcv_dt = '21APR2022'd; output;
cova_evnt_efcv_dt = '20APR2022'd; output;
cova_evnt_efcv_dt = '23JAN2020'd; output;
cova_evnt_efcv_dt = '01JAN2021'd; output;
cova_evnt_efcv_dt = '30MAR2022'd; output;
cova_evnt_efcv_dt = '21MAR2022'd; output;
cova_evnt_efcv_dt = '28JUN2022'd; output;
format cova_evnt_efcv_dt date9.;
run;
proc sql;
select
*
from
want
where
cova_evnt_efcv_dt between mdy(1, 1, year(today()) - 1) and today();
quit;
`today()` would be based on when you run the report. If you automated it every Monday, you wouldn't have to look at it anymore.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
thank you!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@LMSSAS wrote:
No, I'm not always looking a year back. it will be YTD starting every year. Unless the customer changes their mind, which is always possible.
thank you!
In that case, use this (giving credit to @japelin )
and cova_evnt_efcv_dt between intnx('year',today(),0,'b') and today()
Paige Miller