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

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

jwillis — Wed, 21 May 2014 15:59:51 GMT

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.

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

art297 — Wed, 21 May 2014 16:05:49 GMT

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.

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

jwillis — Wed, 21 May 2014 16:35:01 GMT

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

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

art297 — Wed, 21 May 2014 17:00:21 GMT

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

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

Ksharp — Thu, 22 May 2014 13:57:28 GMT

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

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

jwillis — Thu, 22 May 2014 14:17:13 GMT

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;

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

jwillis — Thu, 22 May 2014 14:35:25 GMT

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!

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

RW9 — Thu, 22 May 2014 15:04:03 GMT

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.

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

jwillis — Thu, 22 May 2014 15:15:47 GMT

RW9,

Thank you!

I am connecting to an Oracle database.

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

Ksharp — Sat, 24 May 2014 09:53:09 GMT

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