PROC SQL QUESTION. How to add a row number to a table observation created using SQL.

[jwillis]

Hi all.

Does anyone have a way to convert the datastep below into an equivalent SQL statement?

/*** add a variable that can be used to find missing rows and exclueded rows ***/

data have;

      length row 8;

      set have;

row = _n_;

run;

Thank you.

[art297 · Accepted solution]

The only way I am familiar with is the unsupported monotonic() function.  e.g.

:

proc sql;

  create table want as

    select monotonic() as row, *

      from sashelp.class

  ;

quit;

However, while I've never seen it fail, it ISN'T a supported function, thus might fail when you least expect.

[jwillis]

Dear Arthur,

Thank you!  I learned a new function.  I have to run through millions of records and I would like to run through them in one SQL rather than a SQL and a datastep. I did not find the monotonic function in the SAS book SAS 9.3 Functions and CALL Routines, Reference.  If I have to check to see if the function failed,  I'm more comfortable running millions of records through the SQL and datastep routine.

http://support.sas.com/resources/papers/proceedings14/1277-2014.pdf

[art297]

I totally agree with Howard and your decision to add a datastep to the process.  The "official" warning from SAS can be found at:

15138 - Support for the MONOTONIC() function in PROC SQL

[Ksharp]

So you don't need to jump into the risk of monotonic() by using ods .

ods listing close;
ods output sql_results=want;
proc sql number;
select * from sashelp.class;
quit;
ods listing;

Xia Keshan

[jwillis]

Dear Ksharp;

Please explain your code and logic.  It appears that I have to wrap my current sql in ODS but I do not follow your instructions.  I have

proc sql;

   drop table xxxxxxxx;

   connect to oledb as finance ( connection code here ));

   create table xxxxxxxx as

   select     I_want_row_number_here,

                  Q.variables

   from connection to finance

                                    (

                                    select  variables

                                     from  remote_connection_table

                                    where ( selection criteria here )

                                    ) as Q

;

quit;

[Ksharp]

If you can see the result at your destination (whatever it is, html listing. pdf), I guess you can use it . NUMBER option just add a count variable in the destination, so I believe it is a safe way . Can you don't create table ,that will not direct any output into destination.

ods listing close; /* <-------- close your destination which you  have already open*/

ods html close;

ods output sql_results=want;

proc sql number;

   connect to oledb as finance ( connection code here ));

   select       Q.variables

   from connection to finance

                                    (

                                    select  variables

                                     from  remote_connection_table

                                    where ( selection criteria here )

                                    ) as Q

;

quit;

ods html;

ods listing;

Xia Keshan

[jwillis]

Dear Ksharp;

Thank you for the code.  You taught me something I did not know.  Is there any reason why I cannot simply change my code from  "proc sql;" to "proc sql number"?  I have not found any posts on Google saying I should not use "proc sql number".

http://www2.sas.com/proceedings/sugi29/268-29.pdf

Thank you!

[RW9]

I believe the ods output is needed as it is taking the default "print" of the sql and re-directing it back to a dataset.  Much like if you do a proc print without specifying noobs.

KSharp, perhaps you could confirm, it would only do 1-number of observations, you couldn't for instance, do groupings on that?

I just wanted to add here on the OP, I notice that your using pass through.  I.e. the inner most SQL will be passed onto the database for processing.  You may then want to check if the database itself has an inbuilt function to assign row numbers - SQL Server/Oracle have ROW_NUMBER() with which you can create unique ID's per groupings.  OC doc: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm

Unfortunately this wouldn't work if your not connected to a DB.

[jwillis]

RW9,

Thank you!

I am connecting to an Oracle database.