Good afternoon,
%let startdate = '2017-01-01'; %let enddate = '2018-07-01';
%let start_dt = '01jan2017:00:00:00'dt; %let end_dt = '01jul2018:00:00:00'dt;
I want to automate this Proc SQL so that whenever I run the program it would automatically get data from the most recent month back to fourteen months ago. Today is the 18th of July, I do not want the first 18 days of today's month, nor the last twelve days of fourteen months ago accounted for. How can I arrange these %let statements reflect my wishes? Thank you!
It's easier to first show it using a datastep :
data _null_ ;
seed = date() ;
enddate = intnx('month',seed, 0,'b') ; /* move to the beginning of current month */
startdate = intnx('month',seed,-18,'b') ; /* move to the beginning of the 18th month ago */
/* put these into macro variables */
call symput('STARTDATE',cats("'",put(startdate,yymmdd10.),"'")) ;
call symput('ENDDATE' ,cats("'",put(enddate ,yymmdd10.),"'")) ;
/* use dhms(date,h,m,s) to create a datetime */
call symput('START_DT' ,cats("'",put(dhms(startdate,0,0,0),datetime19.),"'d")) ;
call symput('END_DT' ,cats("'",put(dhms(enddate,0,0,0) ,datetime19.),"'d")) ;
run ;
You could then take that and convert it to use %LET and %SYSFUNC if necessary.