topic Re: Sequence in SAS in SAS Programming

Sequence in SAS

deleted_user — Mon, 22 Sep 2008 17:02:39 GMT

Hello SAS Gurus!!!
I need your help (once again). This time regarding the sequence in SAS.

Could you please let me know if there is any SAS Equivalent to the "Sequence" in Oracle.

Actually I need a common sequence number which can be used across the multiple datasets to generate unique sequence number every time.

e.g. If for Dataset A, the sequence number reached till say 100, the sequence in dataset B should start from 101 and likewise.

In oracle I have used Sequence and Sequence.nextVal to get the auto incremented value. Need to get the similar feature in SAS.

Any help regarding this would be highly appreciated.

regards
Kapil Agrawal

Re: Sequence in SAS

deleted_user — Mon, 22 Sep 2008 17:37:07 GMT

Kapil,

SAS does not have a sequence object like Oracle does. I would think your options are:

1) For one table solution you can use function monotonic(). Note that it is experimental in 9.1.3.

proc sql;
create table test as
select monotonic() as rownum LABEL ='Row Number',
a.*
from test1 as a
;
quit;

2) For multi-table solution you will have to keep track of it yourself.

ikp.

Re: Sequence in SAS

Cynthia_sas — Tue, 23 Sep 2008 08:44:55 GMT

Hi:
For simple queries, monotonic may work. However, I call your attention to this Tech Support note:
http://support.sas.com/kb/15/138.html
which says that:
"The MONOTONIC() function is not supported in PROC SQL. Using the
MONOTONIC() function in PROC SQL can cause missing or non-sequential
values to be returned."

cynthia

Re: Sequence in SAS

deleted_user — Tue, 23 Sep 2008 16:17:36 GMT

Cynthia,

Thanks for pointing it out.

ikp

Re: Sequence in SAS

deleted_user — Wed, 24 Sep 2008 15:52:18 GMT

Dear ikp and Synthia.

Thanks for providing the info. that Sequence does not work in SAS the way it does in Oracle.

I have found the way to handle it in SAS.

But the information provided by you saved a lot of time.

Thanks a ton 🙂

Regards
Kapil Agrawal