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;
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;
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;
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;
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?
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.