BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
4 REPLIES 4
Peter_C
Rhodochrosite | Level 12
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
Not applicable
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
Not applicable
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
Not applicable
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

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