How to create a dynamic date range

[lbridges225]

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

[Patrick]

@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)

[lbridges225]

@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!

[ChrisNZ]

> 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

[Patrick]

@ChrisNZ

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

[ChrisNZ]

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.

[lbridges225]

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

[SuryaKiran]

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') 

[ChrisNZ]

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.

[lbridges225]

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.

[SuryaKiran]

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