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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: