SAS Proc sql row number

Question

How do I get the row number of an observation in proc sql, similar to _N_ for a datastep in proc sql?

For example

proc sql outobs=5;
    select case mod(<something>, 2)
    when 0 then "EVEN"
    else "ODD" 
    end
    from maps.africa
end;

Want:

Row
----------
    1 odd
    2 even
    3 odd
    .
    .
    .

Answer 1

Monotonic() does exist and in some cases can be helpful, but it is not identical to a row number, and can be dangerous to use, particularly given SQL is a heavily optimized language that will happily split your query into multiple threads - in which case monotonic() would fail to accomplish what you want. It in particular can behave differently on different datasets, on different SAS installations, or even simply on different days.

The safe way to do this is to create a view with _n_ copied into a permanent variable.

data africa_v/view=africa_v;
  set maps.africa;
  rownum=_n_;
run;

proc sql;
  select case mod(rownum, 2)
    when 0 then "EVEN"
    else "ODD" 
    end
    from africa_v;
quit;

This adds nearly no overhead - a few milliseconds - and achieves the same result, but with the safety to be confident you have the right ordering. The two queries (this and shipt's) run in nearly identical times on my machine, well within the margin of error (2.95s vs 2.98s for all records).

Answer 2

Use the monotonic() function. Whilst in the past I have read that this is an undocumented function (it is true that it does not appear on the sas website, there is at least one sas 'proceedings' document which makes use of it heavily

For example:

proc sql outobs=5;
    select case mod(monotonic(), 2)
    when 0 then "EVEN"
    else "ODD" 
    end
    from maps.africa;
quit;

will achieve your aim.