Proc SQL - Create Unique Index with PW=

[JoeMadden]

Afternoon All,

Anyone able to create a Unique Index with Proc SQL and the PW= option for a protected dataset?

Works fine when creating a simple index (example);

Proc Sql;

     Create Index MY_COL on MY_DATASET (PW=MYPASS);

Quit;

However, I've tried different permutation of syntax and failed miserably, the only way I have been able to get it to successfully work is to remove the dataset protection

then apply the UI and then re-apply the protection.

No Protection (OK)

Proc Sql;

     Create Unique Index MY_INDEX on MY_DATASET (COL1,COL2);

Quit;

Protection (???) - All incorrect syntax

Proc Sql;

     Create Unique Index MY_INDEX on MY_DATASET (PW=MYPASS COL1,COL2);

     Create Unique Index MY_INDEX on MY_DATASET ((PW=MYPASS) COL1,COL2);

     Create Unique Index MY_INDEX on MY_DATASET (PW=(MYPASS) COL1,COL2);

     Create Unique Index MY_INDEX on MY_DATASET (COL1,COL2) (PW=MYPASS);

Quit;

Thank you in advance!

[RW9]

I believe, and only guessing here, that the (pw=) option is only available on the from part of SQL.  I would assign the pw in a post step:

proc sql;

     create unique index MY_INDEX

     on     MY_DATASET (COL1,COL2);

quit;

data my_dataset (pw=xyz);

     set my_dataset;

run;

[JoeMadden]

Thanks RW9, I suspected this maybe the case!

I'd be interested to know if the unique index is lost in the second step when dataset is re-created.

Joe.

[Ksharp · Accepted solution]

How about this one :

data class;
 set sashelp.class;
 run;
 
proc datasets lib=work nodetails nolist;
modify class(pw=abc);
index create name/unique;
quit;

Xia Keshan

[JoeMadden]

Thank you Xia!