How to create Surrogate Key and SCD type 2 in SAS Studio by code

[akshay3]

Hi,

I want to create Surrogate Key and want to apply slowly changing dimension to a dataset from SAS studio by using SAS coding.

Can anyone give me the outline/concept How can we do this by code?

I have a Test dataset and observations from that will update on regular basis. I want to create a ID which will auto increment for every new test given by a specific participant. and ID will start from "1" and it will automatically increment if there is another test given by the same participant at different time.

Thanks

[LinusH]

This is somewhat a complex task, given your detail requirements.

This is implemented in Data Integration Studio, end there's a reason why there exist ETL tools, so we don't have to reinvent the wheel.

If you don't know how to start, the risk is that you can't complete the task by yourself.

So I suggest that you try to involve someone  directly in this job.

[Kurt_Bremser]

Could you give us an example for your dataset, how you want the surrogate key to look for that data, and what you get when the update happens?

[akshay3]

I'm having data like this,

Subject_ID   Test_Name  Start_Date End_Date

123               ABC

123               ABC

123               ABC

123               XYZ

124               ABC

124               ABC

124               XYZ

and respective dates for those ID's, I want to create one more Variable ROWID for above dataset which will have sequential count for every Subject_ID  and Test_Name. It should be depends on both these variables. Like an example: If there is a first test ABC for subject 123 then ROWID will '1' and for next test ABC for same subject 123 ROWID will '2'  but if Test is ABC and subject is 124 then it should start from '1'.

Output like below:

ROWID

1

2

3

1

1

2

1

[Kurt_Bremser]

Now that's simple:

proc sort data=have;
by subject_id test_name start_date;
run;

data want;
set have;
by subject_id test_name;
if first.test_name
then rowid =1;
else rowid + 1; /* use of a SUM statement causes automatic retain */
run;