Auto-increment option for numeric datatype?

[deleted_user]

I've been looking for auto-increment options for auto incrementing a primary key by 1. I know you could use _n_ for getting the observation row, but how do I combine this with appending a tabel?

I got a dimension table where the combination of 4 colums is unique. This is a pain in the ass for building a cube with OLAP Cube Studio where you need to define 1 key. Message was edited by: Plaksnor

[Peter_C]

If you can use a data step to process your dimension table: [pre] data new ;
set dimens.table ;
compo_key = catx( '-', of keypart1-keypart4 ) ;
row +1 ;
run ; [/pre]
The CATX() function will handle mixed data-types among the variables that it concatenates.[pre] row +1 ;[/pre] will provide a row number variable in the "WORK.NEW" output table.

Is that what you need?

PeterC

[deleted_user]

Yeah, this has pretty much the same results as using the data step I had in mind, which wasn't the hardest part:

data new;
id=_n_;
set dimens.table;
run;

The only thing I wanted to do now is to extend this dataset with new data without changing the current combination of id and the colums of dimens.table.

A PROC SQL with union of both tables (one with current data and one with new data) won't work, and it's not possible afaik to add new colums (the id) in a PROC APPEND.

Tnx for your help so far 🙂

[deleted_user]

Nevermind, found the solution:

Based on a filled Dimension-table:

1) Substract all records from your source which won't appear in your dimension table (select distinct ..where not in..). This is going to be the new dimension table data.

2) Use a proc append (or whatever you want) to append your current dimension table.

3) Give the dimension table new id's, even if they already exist. The data shouldn't be rearranged or sorted, so this way the fact table still refers to the same dimension data.


However, there should be a better way, cause my way seems to be tricky (you really want to avoid your data from being changed, especially for a complete dataset/dimension table in a starschema)

I'm still open for new ideas, bring them on! Ps. using the id based on '-' separated key will work better for me!

[deleted_user]

A real reduction of sql data management is offered by the special option for PROC APPEND that seems little known, [pre] UNIQUEsave = Replace[/pre] It replaces rows with matching key, and appends new rows.
( however even with this option this proc append won't add columns. )
The only reason I can think of to explain why UNIQUEsave=Replace is seldom offered as a solution for your situation is that this option value is difficult to find documented and "Scalable Performance Data Engine" on which it depends, seems outside of "mainstream" SAS even though it is part of base SAS and a significant enhancement for performance.
Scalable Performance Data Engine is certainly worth a look for scaling up performance. ... ... It has a big brother in the SPD Server for situations where performance pays.

PeterC