SAS-Problem-2:Row Numbers in SAS




Assigning Row Numbers In SAS
(Click on the images to Zoom)




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

  1. gravatar

    # by HG2 - December 4, 2012 at 10:03 PM

    Brilliant - thank you for this!

Blog Widget by LinkWithin

Search this blog..

Loading