Analytics Tools

Below is a method of assigning row numbers to a sas dataset.Suppose I have a dataset which I create using the following code:

DATA ROW_NUM_DATA;

INPUT PATIENT$ 1-2 SEX $ 3-4 AGE 5-7 PS 8-9;

DATALINES;

A F 45 0

B M 63 2

C M 57 1

D F 72 3

E F 39 0

F M 57 1

G M 63 0

;

RUN;

The dataset formed looks like this.

Now I want to create another column (or Variable) which would have the row number as its values so a dataset like this.

Solution: This can be done in two ways one would be in ‘DATA’ step and the other would be in ‘Proc SQL’.

1. Data Step:

DATA DATA_FOR_ROW_NUM;

SET ROW_NUM_DATA;

FORMAT ROW_NUM 1.;

ROW_NUM=_N_;

RUN;

Comment= Here ‘_N_’ acts as an in built row number. _N_ is an automatic variable created by every DATA step. _N_  is initially set to 1. Each time the DATA step loops past the DATA statement, the variable _N_ increments by 1. The value of _N_ represents the number of times the DATA step has iterated.

2. PROC SQL:

PROC SQL;

CREATE TABLE SQL_FOR_ROW_NUM AS

SELECT *,MONOTONIC() AS ROW_NUM

FROM ROW_NUM_DATA

;

QUIT;

Comment: Monotonic acts as a function which assigns row numbers.Since PROC SQL uses a relational database concept that is different from the DATA step, we can’t get the iteration number from the PROC SQL procedure. An undocumented function, MONOTONIC( ), in PROC SQL that can generate very similar result as the _N_ in DATA step