06-14-2017 05:03 PM
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')
06-14-2017 05:56 PM - edited 06-14-2017 05:57 PM
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)
06-15-2017 02:24 PM
@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')
06-14-2017 07:53 PM
> 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;
06-14-2017 09:42 PM - edited 06-14-2017 09:44 PM
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.
06-15-2017 02:37 PM
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')
06-15-2017 06:04 PM - edited 06-15-2017 06:04 PM
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.
07-11-2017 04:29 PM
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...
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();
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;
or should I be using this data null statement
CALL SYMPUT ("FISCAL_MONTH", FISCAL_MONTH_2017);
07-11-2017 05:01 PM
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.