topic Re: How to create Surrogate Key and SCD type 2 in SAS Studio by code in SAS Programming

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

akshay3 — Tue, 10 Nov 2020 11:56:33 GMT

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

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

LinusH — Tue, 10 Nov 2020 16:02:14 GMT

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.

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

Kurt_Bremser — Tue, 10 Nov 2020 16:52:15 GMT

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?

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

akshay3 — Wed, 11 Nov 2020 03:59:58 GMT

I'm having data like this,

Subject_ID Test_Name Start_Date End_Date

123 ABC

123 XYZ

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

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

Kurt_Bremser — Wed, 11 Nov 2020 05:53:16 GMT

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;