BookmarkSubscribeRSS Feed
lbridges225
Calcite | Level 5

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

10 REPLIES 10
Patrick
Opal | Level 21

@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
Calcite | Level 5

@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
Tourmaline | Level 20

> 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
Opal | Level 21

@ChrisNZ

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

ChrisNZ
Tourmaline | Level 20

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
Calcite | Level 5

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

SuryaKiran
Meteorite | Level 14

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
ChrisNZ
Tourmaline | Level 20

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
Calcite | Level 5

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
Meteorite | Level 14

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 4529 views
  • 2 likes
  • 4 in conversation