Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /**********************************************
- A rundown of using %sysfunc to always have the
- date needed. This might be able to help eliminate
- hard coding dates into reports.
- **********************************************/
- /*Basic Day Functions*/
- %let today=%sysfunc(today(), date9.); %put &today.;
- %let yesterday = %sysfunc(intnx(day,%sysfunc(today()),-1),date9.); %put &yesterday.;
- %let tomorrow = %sysfunc(intnx(day,%sysfunc(today()),+1),date9.); %put &tomorrow.;
- /*Weekly Functions*/
- %let week_from_today = %sysfunc(intnx(day,%sysfunc(today()),+7),date9.); %put &week_from_today.;
- %let week_ago_from_today = %sysfunc(intnx(day,%sysfunc(today()),-7),date9.); %put &week_ago_from_today.;
- %let week_ago_yesterday = %sysfunc(intnx(day,%sysfunc(today()),-8),date9.); %put &week_ago_yesterday.;
- /*if you run previous'day of the week' on that day, it will return today()*/
- %let Previous_Sunday = %sysfunc(intnx(week1.1,%sysfunc(today()),0),date9.); %put &Previous_Sunday.;
- %let Two_Sundays_Ago = %sysfunc(intnx(week1.1,%sysfunc(today()),-1),date9.); %put &Two_Sundays_Ago.;
- %let Next_Sunday = %sysfunc(intnx(week1.1,%sysfunc(today()),+1),date9.); %put &Next_Sunday.;
- %let Previous_Monday = %sysfunc(intnx(week1.2,%sysfunc(today()),0),date9.); %put &Previous_Monday.;
- %let Two_Mondays_Ago = %sysfunc(intnx(week1.2,%sysfunc(today()),-1),date9.); %put &Two_Mondays_Ago.;
- %let Next_Monday = %sysfunc(intnx(week1.2,%sysfunc(today()),+1),date9.); %put &Next_Monday.;
- %let Previous_Tuesday = %sysfunc(intnx(week1.3,%sysfunc(today()),0),date9.); %put &Previous_Tuesday.;
- %let Two_Tuesdays_Ago = %sysfunc(intnx(week1.3,%sysfunc(today()),-1),date9.); %put &Two_Tuesdays_Ago.;
- %let Next_Tuesday = %sysfunc(intnx(week1.3,%sysfunc(today()),+1),date9.); %put &Next_Tuesday.;
- %let Previous_Wednesday = %sysfunc(intnx(week1.4,%sysfunc(today()),0),date9.); %put &Previous_Wednesday.;
- %let Two_Wednesdays_Ago = %sysfunc(intnx(week1.4,%sysfunc(today()),-1),date9.); %put &Two_Wednesdays_Ago.;
- %let Next_Wednesday = %sysfunc(intnx(week1.4,%sysfunc(today()),+1),date9.); %put &Next_Wednesday.;
- %let Previous_Thursday = %sysfunc(intnx(week1.5,%sysfunc(today()),0),date9.); %put &Previous_Thursday.;
- %let Two_Thursdays_Ago = %sysfunc(intnx(week1.5,%sysfunc(today()),-1),date9.); %put &Two_Thursdays_Ago.;
- %let Next_Thursday = %sysfunc(intnx(week1.5,%sysfunc(today()),+1),date9.); %put &Next_Thursday.;
- %let Previous_Friday = %sysfunc(intnx(week1.6,%sysfunc(today()),0),date9.); %put &Previous_Friday.;
- %let Two_Fridays_Ago = %sysfunc(intnx(week1.6,%sysfunc(today()),-1),date9.); %put &Two_Fridays_Ago.;
- %let Next_Friday = %sysfunc(intnx(week1.6,%sysfunc(today()),+1),date9.); %put &Next_Friday.;
- %let Previous_Saturday = %sysfunc(intnx(week1.7,%sysfunc(today()),0),date9.); %put &Previous_Saturday.;
- %let Two_Saturdays_Ago = %sysfunc(intnx(week1.7,%sysfunc(today()),-1),date9.); %put &Two_Saturdays_Ago.;
- %let Next_Saturday = %sysfunc(intnx(week1.7,%sysfunc(today()),+1),date9.); %put &Next_Saturday.;
- /*example*/
- PROC SQL;
- CREATE TABLE temp AS
- SELECT
- count(*)
- FROM table_i_want
- WHERE
- date_field BETWEEN "&week_ago_yesterday."d AND "&yesterday."d
- ;
- run;
- /*Monthly Functions*/
- %let First_of_curMth = %sysfunc(intnx(month,%sysfunc(today()),0),date9.); %put &First_of_curMth.;
- %let Last_day_of_curMth = %sysfunc(intnx(month,%sysfunc(today()),0, end),date9.); %put &Last_day_of_curMth.;
- %let First_of_preMth = %sysfunc(intnx(month,%sysfunc(today()),-1),date9.); %put &First_of_preMth.;
- %let Last_day_of_preMth = %sysfunc(intnx(month,%sysfunc(today()),-1, end),date9.); %put &Last_day_of_preMth.;
- %let First_of_nextMth = %sysfunc(intnx(month,%sysfunc(today()),+1),date9.); %put &First_of_nextMth.;
- %let Last_day_of_nextMth = %sysfunc(intnx(month,%sysfunc(today()),+1, end),date9.); %put &Last_day_of_nextMth.;
- /*Quarterly Functions*/
- %let First_of_curQtr = %sysfunc(intnx(qtr,%sysfunc(today()),0),date9.); %put &First_of_curQtr.;
- %let Last_day_of_curQtr = %sysfunc(intnx(qtr,%sysfunc(today()),0, end),date9.); %put &Last_day_of_curQtr.;
- %let First_of_preQtr = %sysfunc(intnx(qtr,%sysfunc(today()),-1),date9.); %put &First_of_preQtr.;
- %let Last_day_of_preQtr = %sysfunc(intnx(qtr,%sysfunc(today()),-1, end),date9.); %put &Last_day_of_preQtr.;
- %let First_of_nextQtr = %sysfunc(intnx(qtr,%sysfunc(today()),+1),date9.); %put &First_of_nextQtr.;
- %let Last_day_of_nextQtr = %sysfunc(intnx(qtr,%sysfunc(today()),+1, end),date9.); %put &Last_day_of_nextQtr.;
- /**********************************************
- Change the arguments around to get the exact
- date frame needed.
- EXAMPLE
- Each quarter a report is needed to complie
- the previous two quarters.
- **********************************************/
- %let First_day_two_qtrs_ago = %sysfunc(intnx(qtr,%sysfunc(today()),-2),date9.); %put &First_day_two_qtrs_ago.;
- PROC SQL;
- CREATE TABLE previous_two_quarters_report AS
- SELECT
- *
- FROM Database_table
- WHERE
- date_field BETWEEN "&First_day_two_qtrs_ago."d AND "&Last_day_of_preQtr."d
- ;
- run;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement