What is Alternative to _N_ in PROC SQL?

SQL is a short name for Structured Query Language. SQL interacts with a large number of data and simplifies that for user.  We try to implement a part of SQL in  SAS and call it PROC SQL. This hybrid form makes it a  powerful tool to solve problems.usersSQL syntax and PROC SQL differ in some ways, to have a little overview see this code. This is the basic format of code,

PROC SQL;
SELECT  COLUMN_NAME
FROM TABLE_NAME
WHERE CONDITION
GROUP BY COLUMN_NAME
;
QUIT;

What is _N_ ?

In Simple words _N_ is the count of the number of iterations in any PROC SQL program. Sometimes it is called auto number drive in SAS too. Here is an example. if you want to access the first 239 records then your condition will be “if _N_ <= 239”.  

One method to use data step, you can check out this,

DATA TEST();
SET TEST;
TEST_FIELD=_n_;
RUN;

Also, for SQL PROC, check this one

PROC SQL;
CREATE TABLE TEST() AS
SELECT *,MONOTONIC() AS TEST_FIELD
FROM TEST;
QUIT;

Now the main question is,  what is the alternative to _n_ in PROC SQL?  MONOTONIC() function, is an alternate to _N_ in the data step. To understand it better, let’s check some examples.

Example 1 – To select a row number between 10 to 25.

PROC SQL;
CREATE TABLE table_name AS
SELECT *
FROM source
WHERE MONOTONIC() BETWEEN  10 and 25;
QUIT;

Example 2 – To Create a row number

PROC SQL; 
CREATE TABLE table_name AS 
SELECT  MONOTONIC AS number
FROM source;
QUIT;

Now that we know, syntax and differences, let’s create our own data set and apply our knowledge, to Create a row number.

Create a dataset-Input

To print the data use PROC PRINT DATA=scores;This is the Output of the data. 

The dataSet Output

Method 1:

So First Method is to create a row named studentCount_1 which counts _N_.

data scores_one;
set scores;
studentCount_1 = _N_;
run;

To add using _N_

Output:

Method 2:

Another method is using a monotonic function where the output is similar. check out studentCount_2.

proc sql;
create table scores_two as
select *,
monotonic() as studentCount_2
from scores;
quit;

Output:

The output of the above code is given below, Here at this point, you know two alternative methods to create another row using two different methods.