Help using Base SAS procedures

Proc SQL - Create Unique Index with PW=

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Proc SQL - Create Unique Index with PW=

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! Smiley Happy


Accepted Solutions
Solution
‎08-25-2015 10:52 PM
Super User
Posts: 10,044

Re: Proc SQL - Create Unique Index with PW=

Posted in reply to JoeMadden

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

View solution in original post


All Replies
Super User
Super User
Posts: 7,988

Re: Proc SQL - Create Unique Index with PW=

Posted in reply to JoeMadden

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;

Occasional Contributor
Posts: 8

Re: Proc SQL - Create Unique Index with PW=

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.

Solution
‎08-25-2015 10:52 PM
Super User
Posts: 10,044

Re: Proc SQL - Create Unique Index with PW=

Posted in reply to JoeMadden

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

Occasional Contributor
Posts: 8

Re: Proc SQL - Create Unique Index with PW=

Thank you Xia!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 313 views
  • 1 like
  • 3 in conversation