Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Regular Contributor
Posts: 217
Accepted Solution

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

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.


Accepted Solutions
Solution
‎05-21-2014 12:05 PM
PROC Star
Posts: 7,363

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

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.

View solution in original post


All Replies
Solution
‎05-21-2014 12:05 PM
PROC Star
Posts: 7,363

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

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.

Regular Contributor
Posts: 217

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

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

Smiley Happy

PROC Star
Posts: 7,363

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

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

Super User
Posts: 9,682

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

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

Regular Contributor
Posts: 217

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

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;

Super User
Posts: 9,682

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

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

Regular Contributor
Posts: 217

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

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!

Super User
Super User
Posts: 7,405

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

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.

Regular Contributor
Posts: 217

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

RW9,

Thank you!

I am connecting to an Oracle database. 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 34534 views
  • 9 likes
  • 4 in conversation