Identify Column Support

[mikegia5]

SAS should include an IDENTITY column attribute to provide compatibility with other database systems.  In my use case I would like to develop code that is 100% compatible between DB2 and SAS by simply modifying the LIBNAME statement.  I can simulate IDENTITY column functionality by generating my own value, but that would not work for code that has already been developed for another database like DB2.  I would need to know that I am in either a DB2 or SAS database environment and then have different code paths.  Or eliminate the use of the DB2 IDENTITY column and always generate a number.  Both choices are not feasible at the moment.

Per Wikipedia:

"An identity column is a column (also known as a field) in a database table that is made up of values generated by the database. This is much like an AutoNumber field in Microsoft Access or a sequence in Oracle. Because the concept is so important in database science, many RDBMS systems implement some type of generated key, although each has its own terminology"

[mikegia5]

I opened a case/track on this and was told to move it to this community, which I did on the same day, but it's been here for over 2 years without a single comment.  Is this the proper forum to submit an official request for enhancement?

Mike Giaquinto
Wells Fargo

[ballardw]

No comments doesn't mean a lot in this section of the forum. Many comments are in the nature of "workarounds". Some comments are clarification of the purpose.

I would say the more important bit would be the number of supports, the number next to that "thumbs up" in the upper left of the page. No supports means the users of this forum aren't seeing a need but not everyone visits the ballot ideas part of the forum.

With the SAS Institute aiming new ideas at the Viya platform I suspect that unless ballot ideas are incorporated there they won't be. The time might be awhile even for accepted ideas.

[Tom]

I suspect there is not much support for this because it does not really have any value for normal statistical analysis tasks.  Sounds like it might be more valuable in a transactional database application which is not a core reason that people use SAS.

[Reeza]

Very small issue, it's identity column not identify so if people search this may not come up very high for them to realize it. 

That being said, SAS is for data analysis. I wouldn't expect this functionality in SAS BASE, but would in SAS DI Studio though. 

[mikegia5]

Thanks all - I see my typo now in the subject but not in the body, so hopefully the body is included when searching?

And yes, I do use SAS for much more than just data analytics, although I do some of that too.  I find SAS very powerful as a programming language and can develop solutions that would take much longer in other languages.  I'm a mainframe software engineer - going on 45 years of experience, and I first started using SAS in 1987, so I've had plenty of time to develop bad habits!    If I had known that IDENTITY wasn't supported, I would have coded an alternative right from the start.

This particular software is for compliance monitoring, and I received a patent two months ago for it - very proud of that! 

[Tom]

Make sure they do a better job than Oracle (or at least the way Oracle was used by the application I was working with).  I just had a program that had been running for over 10 years without issue break because this application generated a unique id column that was larger than a billion.  For a table with only 11 observations (and never had any observations deleted.)  The application seems to be using only one sequence generator for ALL of the tables instead of a separate sequence for each table.