0

I am using MYSQL. Basically I am trying to generate a dataset based on my own defined date range. Can someone share how I am able to do a looping like in SAS? example: 

 %macro date_loop(start,end);
 /*converts the dates to SAS dates*/
 %let start=%sysfunc(inputn(&start,anydtdte9.));
 %let end=%sysfunc(inputn(&end,anydtdte9.));
 /*determines the number of months between the two dates*/
 %let dif=%sysfunc(intck(month,&start,&end));
 %do i=0 %to &dif;
 /*advances the date i months from the start date and applys the DATE9.      format*/
   %let date=%sysfunc(putn(%sysfunc(intnx(month,&start,&i,b)),date9.));
  %put &date;
 %end;
%mend;

%date_loop(2017-01-31,2019-01-31)

Part of my MySQL code: The st_date is self defined.

SET @st_date = '2019-01-31'

DATE_FORMAT(@st_date,'%d/%m/%Y') as POSITION_DATE,
Jimmy Kong
  • 1
  • 1
  • You can only do looping in stored procedures. In SQL you need to join with a table that contains all the values you want to process. – Barmar Feb 26 '19 at 02:53
  • Maybe [this question](https://stackoverflow.com/questions/75752/what-is-the-most-straightforward-way-to-pad-empty-dates-in-sql-results-on-eithe) is helpful. – Barmar Feb 26 '19 at 02:55
  • Editted, sorry for the trouble! – Jimmy Kong Feb 26 '19 at 03:31
  • Have you tried writing a stored procedure that does what you want? Please show your attempt. – Barmar Feb 26 '19 at 03:37
  • 1
    You get lucky and someone out there may be proficient in sas and mysql but you may improve your chances of getting an answer if you pose the question in mysql terms only. – P.Salmon Feb 26 '19 at 07:31

0 Answers0