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
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)
@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!
> 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 |
The to_date() function in the where clause the OP posted indicates pass-through SQL
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.
Sorry I meant to mention that yes this is a pass-thru to an oracle database
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')
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.
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.