BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ani7
Obsidian | Level 7

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;
1 ACCEPTED SOLUTION

Accepted Solutions
CurtisMackWSIPP
Lapis Lazuli | Level 10

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;

View solution in original post

6 REPLIES 6
ballardw
Super User

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
Lapis Lazuli | Level 10

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
Obsidian | Level 7

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
Lapis Lazuli | Level 10

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
Lapis Lazuli | Level 10

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;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 1351 views
  • 0 likes
  • 4 in conversation