Create a table with increasing integers from 1 to N in SQL

[ChanceTGardener]

An alternative approach that avoids combining a DATA step and PROC SQL is to perform the merge in PROC IML (SAS's Interactive Matrix Language). 

options fullstimer;
proc iml;
  N = 10;

  /* create a column vector where element 1 = 1, element 2 = 2,..., element N = N */
  i = t(1:N);

  /* read work.have into a matrix */
  use work.have;
   read all var {"i" "c"} into x;
  close work.have;

  /* populate Nx1 vector of missing values */
  c = j(N,1,.);

  /* for intersecting values in {1..N} and the first column in x (i.e., column i from the work.have data set), replace 
    the missing value with the second column in x (i.e., column c from the work.have data set) */
  c[x[,1]] = x[,2];

  /* create output data set */
  create work.want var {i c};
   append;       
  close work.have; 
quit;

The FULLSTIMER option lists performance metrics to the log, and is helpful when comparing multiple competing approaches. Below are the results from my desktop version. 

NOTE: The data set WORK.WANT has 10 observations and 2 variables.
NOTE: PROCEDURE IML used (Total process time):
      real time           0.02 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              582.78k
      OS Memory          21360.00k

To your point (3) above, this is one alternative to the DATA step + SQL procedure approach that combines everything into one procedure, but it's unclear which is more efficient on your real data. The FULLSTIMER will help you test that.  

[Reeza]

Brute force manual method:

proc sql;
create table integers
(num int);
insert into integers values(0);
insert into integers values(1);
insert into integers values(2);
insert into integers values(3);
insert into integers values(4);
insert into integers values(5);
insert into integers values(6);
insert into integers values(7);
insert into integers values(8);
insert into integers values(9);
quit;

proc sql;
create table want as
select ones.num + tens.num*10 + hundreds.num*100 as total
from integers as ones, integers as tens, integers as hundreds
having total < 130
order by 1;
quit;