topic Re: Create a table with increasing integers from 1 to N in SQL in SAS Programming

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

Rabelais — Thu, 29 Feb 2024 17:36:01 GMT

I'm trying to "translate" this data step to SQL

%let N = 1000;
data want;
	do i=1 to &N;
		output; end;
run;

which simply creates such a table

I tried many codes using functions from other platform/languages but none of them work, for example

/*SNOWFLAKE*/
proc sql;
	select seq4() as number
	from table(generator(rowcount => &N))
	order by 1;
quit;

/*SQL SERVER*/
proc sql;
	SELECT * FROM generate_series(0,&N);
quit;

proc sql;
	SELECT * FROM UNNEST(GENERATE_ARRAY(0, &N));
quit;

Any ideas? Is there a similar function in SAS?

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

ChanceTGardener — Thu, 29 Feb 2024 17:28:08 GMT

Look up the monotonic() function in proc sql.

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

ChanceTGardener — Thu, 29 Feb 2024 17:31:35 GMT

I meant to include the example code below in my last post.

proc sql;
 create table myair as 
  select *
  ,	     monotonic() as rowidx
  from sashelp.air;
quit;

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

ballardw — Thu, 29 Feb 2024 17:36:50 GMT

In general SQL does not support looping structures like DO i= . It also generally does not process records in anything resembling a specific order. So why is this supposed to be done in SQL?

If this just a learning exercise the take away is that this sort of activity is not an SQL strong point.

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

Rabelais — Thu, 29 Feb 2024 17:39:12 GMT

I see that you use an input table and count the rows. But what if N is 10000, 10000000 or bigger? Isn't possible to create such a table without using an input table?

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

FreelanceReinh — Thu, 29 Feb 2024 17:47:35 GMT

Hello @Rabelais,

I think in PROC SQL you might use the undocumented MONOTONIC function -- as ChanceTGardener has already suggested -- and so did I (reluctantly) when someone asked a similar question in 2019 (see Re: DO Loop with OUTPUT in SQL?). So, if you have a dataset at hand with at least N observations (e.g., the 428 obs. in SASHELP.CARS for N=100), you can use something like this:

%let N=100;

proc sql;
create table want(where=(i<=&N)) as
select monotonic() as i from sashelp.cars;
quit;

Otherwise it gets more complicated as you need to create such a dataset (or view) from scratch, e.g., like view BASE below:

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

select 'seed'||repeat(',seed',int(log2(&N)-1)) into :ds
from seed;

create view base as
select ' ' from &ds;

create table want(where=(i<=&N)) as
select monotonic() as i from base;
quit;

Again, this is really not recommended. Use a DATA step instead.

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

Patrick — Fri, 01 Mar 2024 00:20:41 GMT

There is the undocumented (and NOT supported) monotonic() functions mentioned by others. My advice is to never use undocumented functions in a production environment.

You can't do this with SAS ANSI SQL and you don't have to given it's so simple using a SAS datastep.

There are SQL extensions in many databases that allow for creation of such row numbering and if you need this in a database table then you can use explicit passthrough SQL.

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

Tom — Fri, 01 Mar 2024 01:11:30 GMT

This sounds like an XY Problem.

Why do you want make such a dataset?
Why don't you just use a data step since you already showed you know how to that?

Why would you even want to attempt to do it in PROC SQL?

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

Rabelais — Fri, 01 Mar 2024 16:55:00 GMT

@Tom

Curiosity is the lust of the mind!

As a new SAS user, I'm asking myself lot of questions.

Q2 & Q3: (1) Lot of tasks can be done by using different approaches and since I'm working with large datasets (this is not the case since generating even a big sequence is fast) I usually look for the most efficient one. (2) Since I'm new to SAS I often wonder if my code can be improved. (3) Also, sometimes I'm simply curious about different ways of doing the same task.

Q1: see the toy example below (it's just 10 rows, but I'm working with millions).

Basically I need to "expand" the dataset HAVE so that "i" goes from 1 to N.

I was wondering if the second data step and the proc sql could be easily combined into a single proc sql or data step.

I tried using PROC EXPAND to interpolate the missing "i" values, but what it does is rather to replace a missing value with an interpolated one on an already present row, it doesn't add new rows.

data have;
input i c; cards;
7 0.2
3 0.5
6 0.3
run;

data sequence;
	do i=1 to 10;
		output;
	end;
run;

proc sql;
	create table want as select
		a.i, b.c
	from sequence a
	left join have b
	on a.i = b.i;
quit;

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

ballardw — Fri, 01 Mar 2024 16:43:43 GMT

@Rabelais wrote:

@Tom

Q1: see the toy example below (it's just 10 rows, but I'm working with millions).

Basically I need to "expand" the dataset HAVE so that "i" goes from 1 to N.

I strongly suggest that you show an example of what that expansion looks like. I can think of at least two possible different "expansions" but without an idea of the result should look like it isn't worth bothering to write any example code.

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

Rabelais — Fri, 01 Mar 2024 16:53:19 GMT

@ballardw

I added pictures with code's output, i.e. HAVE and WANT datasets

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

Tom — Fri, 01 Mar 2024 16:59:22 GMT

I would think PROC EXPAND could fill out a series for you with a constant value. Perhaps you just did not find the right option for how to specify the constant value you wanted it to use?

But don't bother trying to make SQL do something like iterating. That is not part of the grammar that SQL uses to describe the actions it can take.

If you want to make sure your data always has observations for ID=1 to 10 then do something like this:

data skeleton;
  do id=1 to 10;
    output;
  end;
run;
data want;
  merge skeleton actual_data;
  by id;
run;

No SQL needed (or wanted).

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

ballardw — Fri, 01 Mar 2024 17:18:06 GMT

proc sort data=have;
   by i;
run;

data want;
  merge sequence
        have;
  by i;
run;

Assuming sequence is built as described.

The explicit sort and merge may run faster than SQL as SQL does that in the background for the Join on.

BUT you talk about "millions" of observations. Are any of the I values in HAVE ever duplicated? If so you need to include such in your example and perhaps describe rules involved.

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

Reeza — Fri, 01 Mar 2024 17:28:31 GMT

SQL is designed to manage data and manipulate it, typically not to create new rows/records. Even a pivot isn't a simple operation in SQL.

SAS on the other hand is more data wrangling/analysis friendly.

Right tool for the right job.

Several different sql options are listed here you can play around with different DBs, and different dbs will have different methods.

But not SQL also has the ability to create unique key identifiers on each row automatically, which SAS does not.

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

ChanceTGardener — Fri, 01 Mar 2024 18:30:00 GMT

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.

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

Reeza — Fri, 01 Mar 2024 18:57:10 GMT

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;

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

FreelanceReinh — Sat, 02 Mar 2024 09:49:07 GMT

I've just noticed a minor inefficiency in the code that I copied from my old post: If N happens to be a power of 2 (2, 4, 8, 16, ...), view BASE creates 2N observations, while N would be sufficient. This can be avoided by replacing int(log2(&N)-1) with ceil(log2(&N)-2):

%let N=100;

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

select 'seed'||repeat(',seed',ceil(log2(&N)-2)) into :ds
from seed;

create view base as
select ' ' from &ds;

create table want(where=(i<=&N)) as
select monotonic() as i from base;
quit;