DO Loop with OUTPUT in SQL?

[Junyong]

Currently I am studying how to use SQL. Suppose generate month-end observations using DO and OUTPUT in DATA as follows.

%let start="1jan2000"d;
%let finish="31dec2019"d;
data monthend;
format date yymmddn8.;
do date=&start. to &finish.;
if date=intnx("month",date,0,"e") then output;
end;
run;

Can I do the similar thing in SQL too?

[PaigeMiller]

I'm sure someone can come up with a way to do this in SQL, but it is the wrong tool for the job. Your DATA step is the proper tool to do this kind of looping.

[Cynthia_sas]

Hi:
You can't use SQL for looping like this. SQL does not have the concept of iterative DO loops. The DATA step is the correct tool for the job.

Cynthia

[PGStats]

I don't think you can generate such a series with SAS/SQL without a macro front end. Should be done efficiently with:

%let start="1jan2000"d;
%let finish="31dec2019"d;
data monthend;
format date yymmddn8.;
date = intnx("month", &start., 0, "e");
do while(date <= &finish.);
    output;
    date = intnx("month", date, 1, "e");
    end;
run;

[Patrick]

Possible with Oracle SQL but not SAS SQL.

[FreelanceReinh · Accepted solution]

Hi @Junyong,

The undocumented MONOTONIC function might come in handy, but I wouldn't recommend this because it's undocumented and you need a sufficiently large arbitrary dataset (here, e.g., SASHELP.CARS with its 428>=240 observations) as a basis.

proc sql;
create table monthend as
select intnx('month', &start, i, 'e') as date format=yymmddn8.
from (select monotonic()-1 as i from sashelp.cars
      where calculated i<=intck('month', &start, &finish))
order by date;
quit;

It is much better to use a DATA step for this task.

Edit:

Just for fun: If you don't want to rely on an existing dataset such as SASHELP.CARS, you can create one yourself, let's call it BASE (although, again, PROC SQL is not the ideal tool for this).

proc sql noprint;
create table seed (x num);
insert into seed
set x=0
set x=1;

select 'seed'||repeat(',seed',int(log2(max(intck('month',&start,&finish),1))-1)) into :ds
from seed;

create table base as
select ' ' from &ds;

create table monthend as
select intnx('month', &start, i, 'e') as date format=yymmddn8.
from (select monotonic()-1 as i from base
      where calculated i<=intck('month', &start, &finish))
order by date;
quit;