BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
4 REPLIES 4
deleted_user
Not applicable
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.
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
Cynthia,

Thanks for pointing it out.

ikp
deleted_user
Not applicable
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

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
  • 2082 views
  • 0 likes
  • 2 in conversation