DATA Step, Macro, Functions and more

How to create a dynamic date range

Reply
Occasional Contributor
Posts: 8

How to create a dynamic date range

I need to run a query each month that uses October 1, 2016 up to the most recent month end date.  So in this case I ran the query for 10/1/2016 - 5/31/2017.  Next run will be 10/1/2016 to 6/30/2017.  I'm trying to code this in SAS and this is as far as I've gotten..and I'm 100% new so...

 

 

%let mnth = 5;
%let yr = 2016;
%let fy= %substr(&year,3,2)%sysfunc(putn(%eval(%substr(&year,3,2)+1), z2.));

 

My goal is to no longer have to modify the dates below manually.

WHERE PER.DAY_DATE BETWEEN to_date('10/01/2016','mm/dd/yyyy') and to_date('05/31/2017','mm/dd/yyyy')

 

Thanks everyone

Respected Advisor
Posts: 4,173

Re: How to create a dynamic date range

[ Edited ]
Posted in reply to lbridges225

@lbridges225

Something like below should work

%let startMonth='10/01/2016';
%let endMonth=%unquote(%nrbquote(')%sysfunc(intnx(month,%sysfunc(today()),0,e),mmddyy10.)%nrbquote('));



WHERE PER.DAY_DATE BETWEEN to_date(&startMonth,'mm/dd/yyyy') and to_date(&endMonth,'mm/dd/yyyy')

Using a data step to create and populate the macro variable would make things a bit easier to read and maintain:

data _null_;
  startMonth=intnx('month',today(),0,'e');
  call symputx('startMonth',cats("'",put(startMonth,mmddyy10.),"'"),'G');
run;

And last but not least: As your code looks like Oracle SQL you could also use an Oracle function to achieve what you're after

WHERE PER.DAY_DATE BETWEEN to_date('10/01/2016','mm/dd/yyyy') and LAST_DAY(SYSDATE)

 

Occasional Contributor
Posts: 8

Re: How to create a dynamic date range

@Patrick  The %let statements worked!!  Thank you!

 

How would I do the same thing for this statement, sp that it pulls the next series of dates without manual manipulation?  So as an example, I would need to run this in July for 5/28 - 6/30, and again in August...

 

WHERE PER.DAY_DATE BETWEEN to_date('04/30/2017','mm/dd/yyyy') and to_date('05/27/2017','mm/dd/yyyy')

 

Thank you!

PROC Star
Posts: 1,760

Re: How to create a dynamic date range

Posted in reply to lbridges225

> October 1, 2016 up to the most recent month end date

 

I assume this runs on SAS data. Like this?

 


data SAMPLE;
  format DAY_DATE date9.;
  do DAY_DATE ='06MAY2016'd to '06MAY2018'd by 30;
   output;
 end;
run;

proc sql;
  select * 
  from SAMPLE 
  where DAY_DATE between '01OCT2016'd and intnx('month',today(),-1,'e');
quit;
DAY_DATE
03OCT2016
02NOV2016
02DEC2016
01JAN2017
31JAN2017
02MAR2017
01APR2017
01MAY2017
31MAY2017
Respected Advisor
Posts: 4,173

Re: How to create a dynamic date range

@ChrisNZ

The to_date() function in the where clause the OP posted indicates pass-through SQL

PROC Star
Posts: 1,760

Re: How to create a dynamic date range

[ Edited ]

Possibly. I tooks it as: Oracle is the only syntax I know and I want to know the SAS syntax, since no database is mentionned.

Occasional Contributor
Posts: 8

Re: How to create a dynamic date range

Sorry I meant to mention that yes this is a pass-thru to an oracle database

Frequent Contributor
Posts: 141

Re: How to create a dynamic date range

Posted in reply to lbridges225

Hi @lbridges225,

 

It will be easy if you you Implicit SQL-Passthrough rather than Explicit way. 

Last day for the previous month --> INTNX('MONTH',TODAY(),-1,'e') 

Thanks,
Suryakiran
PROC Star
Posts: 1,760

Re: How to create a dynamic date range

[ Edited ]
Posted in reply to lbridges225

If you dont want implicit pass-thru, you can do this:

 

 
%let enddate=%sysfunc(intnx(month,%sysfunc(today()),-1,e),date9.);

...

where DAY_DATE between '01OCT2016'd and %str(%')&enddate%str(%')

 

 

As a third alternative, there's probaly oracle functions that can do the same thing.

Occasional Contributor
Posts: 8

Re: How to create a dynamic date range

So for my project I had a fiscal calendar.csv uploaded into SAS since our fiscal months here are different from others - which is weird I know.

I think I need to write some data steps to reference this csv and pull the fiscal month start and end dates dynamically.  So far I have a proc sql statement and a data statement....not sure how to proceed...

 

DATA CALENDAR;

INFILE "//sas_env/xxxcl/scm/Fxqhek/fiscal_calendar_month_start_end.csv" DELIMITER = ',' MISSOVER DSD LRECL=32767 FIRSTOBS=2 ;

INFORMAT CALENDAR_DATE MMDDYY10.;

INFORMAT MONTH_START MMDDYY10.;

INFORMAT MONTH_END MMDDYY10.;

FORMAT CALENDAR_DATE MMDDYY10.;

FORMAT MONTH_START MMDDYY10.;

FORMAT MONTH_END MMDDYY10.;

INPUT CALENDAR_DATE MONTH_START MONTH_END ;

IF CALENDAR_DATE = TODAY();

RUN;

 

Is this Proc sql statement the way to go?

 

PROC SQL OUTOBS=1;

CREATE TABLE QUERY_DATES_2017 AS

SELECT * FROM CALENDAR WHERE

FISCAL_MONTH = &FISCAL_MONTH AND

FISCAL_YEAR = &FISCAL_YEAR_2017;

QUIT;

 

or should I be using this data null statement

 

DATA _NULL_;

SET CALENDAR_TODAY;

CALL SYMPUT ("FISCAL_MONTH", FISCAL_MONTH_2017);

RUN;

 

Thanks.

Frequent Contributor
Posts: 141

Re: How to create a dynamic date range

Posted in reply to lbridges225

Hi,

 

What are you trying to achive? There is no clarity in the question. If this question is not related to the earlier one please open a new thread.

 

Thanks,

Surya

Thanks,
Suryakiran
Ask a Question
Discussion stats
  • 10 replies
  • 210 views
  • 2 likes
  • 4 in conversation