Proc sql monotonic vs sql rownum

Reply
New Contributor
Posts: 2

Proc sql monotonic vs sql rownum

Hi! 

Some popeple can help me, i have a dude,

in SQL my code was:

 

update Lista_X set ID=((select max(ID) from Lista_X)+ ROWNUM)

where Date_Mod>='date of update' and ID is null

 

But in sas with proc sql i cant do the rownum

i see in another forum tath equivalent funtion is monotonic()  but the count dont do anyting.

 

 

Smiley Happy thx for the help and sorry for my bad english 

 

Esteemed Advisor
Esteemed Advisor
Posts: 7,253

Re: Proc sql monotonic vs sql rownum

Well, rownum is an oracle database function, not ANSI SQL, so not part of the SAS implementation.  

 

Monotonic() works, but its only in development and can give odd results.

 

Are you connecting to a database with this SQL?  If not then post and example datastep with some data, and what you want as logical position in a dataset really shouldn't matter to the data - i.e. why is rownumber added?

New Contributor
Posts: 2

Re: Proc sql monotonic vs sql rownum

Yes, my sas enterprice guide are conecting to database whit SQL, but my proble is i dont have oracle, my worktool is only sas,

So, i need update my table_list, and the var ID is my indentifier and this must to ascend in funtion of the number of registry.

 

 

Respected Advisor
Posts: 3,840

Re: Proc sql monotonic vs sql rownum

update Lista_X set ID=((select max(ID) from Lista_X)+ ROWNUM)

where Date_Mod>='date of update' and ID is null

 

 

If I understand this right then what you want to do is to maintain a primary key. 

Is Lista_X a SAS table or a database table? If database: Which database and which version?

 

If it's a SAS table then what you want to do is not that hard to achieve via a SAS datastep instead of a SQL.

 

If it's a database: The ones I know all provide in one way or the other options to define auto increment columns so you don't have to care anymore about maintaining a key programatically.

Alternatively you can also create a hash key instead of a sequential key. In SAS you could use functions like SHA(), UUIDGEN() or MD5()

 

Esteemed Advisor
Posts: 5,202

Re: Proc sql monotonic vs sql rownum

Please clarify what your database is. Are you referring to SAS as your client/programming tool, or is the data storage in SAS as well?
If youe data is in Oracle you could use Oracle syntax in SQL explicit pass through.
Data never sleeps
Ask a Question
Discussion stats
  • 4 replies
  • 585 views
  • 0 likes
  • 4 in conversation