topic Re: DO Loop with OUTPUT in SQL? in SAS Programming

DO Loop with OUTPUT in SQL?

Junyong — Wed, 15 May 2019 22:51:24 GMT

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?

Re: DO Loop with OUTPUT in SQL?

PaigeMiller — Wed, 15 May 2019 23:40:55 GMT

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.

Re: DO Loop with OUTPUT in SQL?

Cynthia_sas — Thu, 16 May 2019 00:59:27 GMT

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

Re: DO Loop with OUTPUT in SQL?

PGStats — Thu, 16 May 2019 02:59:12 GMT

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;

Re: DO Loop with OUTPUT in SQL?

Patrick — Thu, 16 May 2019 03:14:41 GMT

Possible with Oracle SQL but not SAS SQL.

Re: DO Loop with OUTPUT in SQL?

FreelanceReinh — Thu, 16 May 2019 10:45:50 GMT

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;