Create a computed column within SAS EG that creates a increasing row number per row

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

Create a computed column within SAS EG that creates a increasing row number per row

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


Accepted Solutions
Solution
‎04-25-2012 03:34 AM
Respected Advisor
Posts: 3,887

Re: Create a computed column within SAS EG that creates a increasing row number per row

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


All Replies
Super User
Posts: 9,671

Re: Create a computed column within SAS EG that creates a increasing row number per row

How about using ODS ?

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


Ksharp

Respected Advisor
Posts: 3,887

Re: Create a computed column within SAS EG that creates a increasing row number per row

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

Solution
‎04-25-2012 03:34 AM
Respected Advisor
Posts: 3,887

Re: Create a computed column within SAS EG that creates a increasing row number per row

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

Occasional Contributor
Posts: 17

Re: Create a computed column within SAS EG that creates a increasing row number per row

Hi all,

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

Steve

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 3386 views
  • 3 likes
  • 3 in conversation