topic Why can't I have different Primary Key and Index in SAS Data Integration Studio? in SAS Programming

Why can't I have different Primary Key and Index in SAS Data Integration Studio?

WorkingMan — Sat, 09 May 2020 13:16:54 GMT

I have tables that I want to set its own primary key and index.

Most of the tables will use ID/RK as Primary Key whereas index will be ID/RK + STARTDT

In SAS Data Integration Studio, setting ID/RK as primary key will also auto set ID/RK as index. If I want to set STARTDT as one of the indexes, it will prompt error saying primary key doesn't have STARTDT as key bla bla. I can provide the exact error message if you need. Only if i set STARTDT as primary key, index will auto include STARTDT.

This is not what I want. If i copy the auto generated code from SAS DI and paste it in EG, i can set as below by executing script in SAS EG with slight modification on my own:
Primary key: ID/RK

Index: ID/RK + STARTDT

In DI Studio, there is no way for me to have Primary key as ID/RK while having ID/RK + STARTDT as index.

Or is there anyway or workaround for me to do so in SAS DI Studio?

Re: Why can't I have different Primary Key and Index in SAS Data Integration Studio?

ChrisNZ — Sun, 10 May 2020 05:31:15 GMT

Isn't the PK unique?

Re: Why can't I have different Primary Key and Index in SAS Data Integration Studio?

WorkingMan — Sun, 10 May 2020 05:34:20 GMT

Yes PK is unique. But we want to optimize the loading speed to ensure it runs at the most efficient way. PK+STARTDT for index is based on past experience and best practice in other non-SAS environment.

Is it a good practice/common way of having PK and index the same variable/column?

Re: Why can't I have different Primary Key and Index in SAS Data Integration Studio?

ChrisNZ — Sun, 10 May 2020 05:45:31 GMT

There is no point whatsoever in adding another variable to create a composite index that includes a unique variable.

The unique variable identifies the one record to retrieve. The other variable serves no purpose.

Re: Why can't I have different Primary Key and Index in SAS Data Integration Studio?

ChrisNZ — Sun, 10 May 2020 06:10:26 GMT

Now if you want to create another index that's a different matter.

Re: Why can't I have different Primary Key and Index in SAS Data Integration Studio?

WorkingMan — Sun, 10 May 2020 08:11:48 GMT

Can i achieve this without running into error in SAS EG?

Re: Why can't I have different Primary Key and Index in SAS Data Integration Studio?

ChrisNZ — Sun, 10 May 2020 11:07:28 GMT

I don't know what you are trying to achieve,

All this code is valid:

data CLASS; 
  set SASHELP.CLASS;
run;
proc sql;
  alter table CLASS add primary key(SEX,NAME);
quit;
proc contents ; run;

data CLASS; 
  set SASHELP.CLASS;
run;
proc sql;
 alter table CLASS(index=(A=(NAME SEX))) add primary key(NAME);
quit;
proc contents ; run;