Proc SQL dating back 14 whole months

Question

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!

Do a google search on date functions for your particular SQL environment. There are all sorts of functions (such as adding a certain number of days/months/years/etc to an existing date.) — Kevin, Jul 18 '18 at 20:17
Do you have a query where you are using the macro variables? It will be helpful for us if you give all the details of your want and have. As a prelim, you can create a date with `mdy` that would be dynamic and then subtract 14 months from it using `intnx`. It'd be a easy job. — samkart, Jul 18 '18 at 20:18

score 0 · Accepted Answer · answered Jul 18 '18 at 20:25

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.

Proc SQL dating back 14 whole months

1 Answers1