topic Re: How to create a dataset with a series of dates with a proc sql? in SAS Programming

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

Ani7 — Thu, 03 Sep 2020 22:28:12 GMT

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;

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

ballardw — Thu, 03 Sep 2020 22:45:45 GMT

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;

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

CurtisMackWSIPP — Thu, 03 Sep 2020 23:04:29 GMT

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;

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

Ani7 — Thu, 10 Sep 2020 13:58:49 GMT

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?

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

CurtisMackWSIPP — Wed, 16 Sep 2020 14:28:05 GMT

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.

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

Kurt_Bremser — Wed, 16 Sep 2020 14:36:41 GMT

Maxim 14. Stay with your data step.

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

CurtisMackWSIPP — Wed, 16 Sep 2020 14:41:37 GMT

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;