topic Re: Proc SQL - Create Unique Index with PW= in SAS Procedures

Proc SQL - Create Unique Index with PW=

JoeMadden — Tue, 25 Aug 2015 14:16:26 GMT

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!

Re: Proc SQL - Create Unique Index with PW=

RW9 — Tue, 25 Aug 2015 14:37:43 GMT

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;

Re: Proc SQL - Create Unique Index with PW=

JoeMadden — Tue, 25 Aug 2015 14:45:02 GMT

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.

Re: Proc SQL - Create Unique Index with PW=

Ksharp — Wed, 26 Aug 2015 02:52:05 GMT

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

Re: Proc SQL - Create Unique Index with PW=

JoeMadden — Wed, 26 Aug 2015 07:39:28 GMT

Thank you Xia!