How to create a dataset with a series of dates with a proc sql?

[Ani7]

Essentially is there a way to recreate this data step with a proc sql?

%let start_date=01Jan2011;
%let end_date=31Dec2017;

/*create table with all available months*/
data _01_dates;
	date_var="&start_date"d;
	do while (date_var<="&end_date"d);
		output;
		date_var=intnx('month', date_var, 1, 's');
	end;
	format date_var date9.;
run;

[ballardw]

Probably. Likely to be a bunch of ugly code. SQL has no concept of loops or iteration per se so you likely have to get into some very ugly and lengthy code.

That can be simplified to

data _01_dates;
   do date_var = "&start_date."d to "&end_date."d;
      output;
   end;
   format date_var date9.;
run;

[CurtisMackWSIPP]

You could do it with macros of course, but you can also do it with the help of any dataset with enough rows.

%let start_date=%sysfunc(mdy(1,1,2011));
%let end_date=%sysfunc(mdy(12,31,2017));
proc sql;
create table junk as
select monotonic() as n1, intnx('month', &start_date, monotonic() -1, 's') as datevar format date10.
from sashelp.Timedata
where calculated datevar < &end_date;
quit;

[Ani7]

I'm on SAS EG and SASHELP.TIMEDATA doesn't exist for me. Is there no way to do this without using an inbuilt dataset?

[CurtisMackWSIPP]

Yes, you can use any table that has more rows than you need dates.  The table is unimportant, it is just a way to get to the monotonic functions that number of times.  That said, I tested this in EG and SASHELP.TIMEDATA exists there in my installation.

[CurtisMackWSIPP · Accepted solution]

You could also do it this way if you are in a macro;

%macro monthsql;
  %let start_date=%sysfunc(mdy(1,1,2011));
  %let end_date=%sysfunc(mdy(12,31,2017));
  proc sql;
  create table junk (datevar date);
  insert into junk (datevar)
    %let thisdate=&start_date;
    %do %until(&thisdate >= &end_date);
      values(&thisdate)
      %let thisdate = %sysfunc(intnx(month, &thisdate, 1, s));
    %end;;
  quit;
%mend;
%monthsql;

[Kurt_Bremser]

Maxim 14. Stay with your data step.