topic Re: index unique in more than one var in SAS Programming

index unique in more than one var

Angel_Saenz — Sat, 10 Sep 2022 17:24:43 GMT

Hi everyone, Is it possible to create unique index on more than one variable?

For example I need unique index in three variables:

proc datasets library=lib1;
modify dataset1;
index create var1 / unique;
index create var2 / unique;
index create var3 / unique;
run;

the problem with that code is that it gives me an error since only one can have the only one

ERROR: Duplicate values not allowed on index var1 for file dataset1

and it's okay for var1 to have duplicates but not in combination with var2 and var3

I thought about concatenating var1, var2, var3 and giving that variable the unique index but I want to see if it is possible to do it directly in those 3 variables.

Because I need that dataset not to have duplicates by combining those 3 variables

Thanks

Re: index unique in more than one var

ballardw — Sat, 10 Sep 2022 17:59:32 GMT

You would need to make sure that your index variables have no duplicate values.

If Var1 =1 appears more than once then you can't use it as a unique index. Period.

If you go the concatenation route and any of those variables are numeric you want to make sure to control how you convert numeric to text values (which would be the only way concatenation makes sense) as the default conversion of numeric to text used by the CAT functions may not yield what you expect.

Re: index unique in more than one var

Angel_Saenz — Sat, 10 Sep 2022 19:28:32 GMT

thanks ll the variables var var2 and var3 have duplicate, thats is ok, because I need is no is the duplicate value in combination of that three
So there is no way to establish a unique to 3 variables together unless you do not have duplicates?

Re: index unique in more than one var

Patrick — Sun, 11 Sep 2022 02:40:54 GMT

You can create a composite index that's unique as documented here.

See sample code below:

data work.demo;
  set sashelp.class;
run;

proc datasets library=work;
  modify demo;
  index create my_index=(sex age height) / unique;
quit;

Re: index unique in more than one var

Angel_Saenz — Mon, 12 Sep 2022 16:04:22 GMT

thanks

Re: index unique in more than one var

Tom — Mon, 12 Sep 2022 16:13:40 GMT

So what you want is that the combination of three variables uniquely identify the observations.

So in terms of sorting you mean something like:

proc sort data=lib1.dataset1 nodupkey ;
   by var1 var2 var3;
run;

In terms of indexes that means you want a composite index, an index based on the value or two or more variables.