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

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

shudsoo — Wed, 25 Apr 2012 06:47:02 GMT

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

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

Ksharp — Wed, 25 Apr 2012 07:22:39 GMT

How about using ODS ?

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

Ksharp

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

Patrick — Wed, 25 Apr 2012 07:34:29 GMT

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

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

shudsoo — Wed, 25 Apr 2012 07:36:25 GMT

Hi all,

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

Steve

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

Patrick — Wed, 25 Apr 2012 07:38:43 GMT

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