BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
shudsoo
Calcite | Level 5

I have a set of data (approx 88 million rows) and wish to add on a computed column that creates a number that increases with every row - sort of like a row number but contained within its own column. The reason is that I need to perform further analysis using an offset of the row number but need the row number to be visible.

Any thoughts or ideas - someone has mentioned the use of MONOTONIC() but cannot find much out there on that one.

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

Monotonic() is an unsupported function and as such not documented in official SAS documentation. Non the less it has been presented at forums and you can find whitepapers (and I believe even some SAS Samples) using it (just Google it).

Monotonic() gives you an ascending rowcount - exactly what you're after.

It appears that there are issues with monotonic() under certain circumstances when used in conjunction with databases (I believe I've read that monotonic() in a sub-select together with Oracle returns unexpected results).

I wouldn't use an undocumented function for a "serious" implementation - but it seems to be o.k. for what you need it for. And actually: monotonic() is a very useful function and I would love to see it supported.

Just define an advanced expression for your computed column with expression monotonic().

Good luck

Patrick

View solution in original post

4 REPLIES 4
Ksharp
Super User

How about using ODS ?

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


Ksharp

Patrick
Opal | Level 21

I doubt that such code can be generated using EG wizards... But nevertheless really cool solution.

Patrick
Opal | Level 21

Monotonic() is an unsupported function and as such not documented in official SAS documentation. Non the less it has been presented at forums and you can find whitepapers (and I believe even some SAS Samples) using it (just Google it).

Monotonic() gives you an ascending rowcount - exactly what you're after.

It appears that there are issues with monotonic() under certain circumstances when used in conjunction with databases (I believe I've read that monotonic() in a sub-select together with Oracle returns unexpected results).

I wouldn't use an undocumented function for a "serious" implementation - but it seems to be o.k. for what you need it for. And actually: monotonic() is a very useful function and I would love to see it supported.

Just define an advanced expression for your computed column with expression monotonic().

Good luck

Patrick

shudsoo
Calcite | Level 5

Hi all,

Thanks for the answers, I have found that MONOTONIC() works well and in fairness gives me exactly the required result.

Steve

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 8429 views
  • 5 likes
  • 3 in conversation