Hi All,
I am wondering how to loop through all datsets in a sas folder (using LIBREF or any other method) and password protect (write and alter) all the datsets.
The functionality should automatically do this for all the datasets in the folder WITHOUT having the need to specify individual dataset names.
Any help would be greatly appreiciated !
Thanks,
Sid.
What kind of operating system are you on and do you just want to apply SAS passwords or do it at the operating system level?
Getting the files is simple using a pipe and the next step will depend upon what level security you wish to apply.
I am on Windows XP . I just want to apply passwords for now.
Could you breif about the "level of security "?
I am just trying to build a macro/datastep which applies passwords (write and alter) to all the sas datsets in a folder.
Thank in advance!
Do you want separate passwords for each file or just one password for the folder? That was principally what I was considering in using the descriptive "levels". As far as I know, read, write and alter are the only three possibilities regardless of whether you use dos or SAS file security.
A brief description of adding passwords in SAS can be found at: http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000995315.htm
Below is a %loop macro which executes a sub-macro for each token in a list. It calls %parmv, which is a parameter validation macro.
Below that is code which does what you want.
I recommend adding %loop and %parmv to your macro autocall library. I use %loop all the time.
Sorry for the long post.
HTH...
/*=====================================================================
Program Name : loop.sas
Purpose : A "wrapper" macro to execute code over a
list of items
SAS Version : SAS 8.2
Input Data : N/A
Output Data : N/A
Macros Called : parmv
Originally Written by : Scott Bass
Date : 24APR2006
Program Version # : 1.0
=======================================================================
Modification History :
Programmer : Scott Bass
Date : 17JUL2006
Change/reason : Added DLM parameter
Program Version # : 1.1
Programmer : Scott Bass
Date : 24DEC2009
Change/reason : Made changes as suggested by Ian Whitlock,
updated header with additional usage cases
Program Version # : 1.2
Programmer : Scott Bass
Date : 13MAY2011
Change/reason : Fixed nested macro scoping errors by explicitly
declaring the iterator and word as local.
Program Version # : 1.3
=====================================================================*/
/*---------------------------------------------------------------------
Usage:
%macro code;
%put &word;
%mend;
%loop(Hello World);
=======================================================================
%let str = Hello,World;
%loop(%bquote(&str),dlm=%bquote(,));
=======================================================================
%macro code();
%put &word;
%mend;
%loop(Hello World,mname=code());
=======================================================================
%macro mymacro;
proc print data=&word;
run;
%mend;
proc datasets kill nowarn nolist;
quit;
data one;x=1;run;
data two;y=2;run;
proc sql noprint;
select memname into :list separated by '|'
from dictionary.tables
where libname = "WORK" and memtype = "DATA"
;
quit;
%loop(&list,dlm=|,mname=mymacro);
=======================================================================
Calling a macro with parameters:
%macro mymacro(parm=&word);
%put &parm;
%mend;
%loop(hello world,mname=mymacro); * this causes a tokenization error ;
%loop(hello world,mname=mymacro()); * no error ;
%mymacro(parm=hi);
Note that the parm is the literal text '&word' (without quotes of course).
See additional details in Nested calls of %loop use case below.
=======================================================================
Nested calls of %loop:
%macro outer;
%put &sysmacroname &__iter__ &word;
%loop(INNER_1 INNER_2 INNER_3,mname=inner)
%mend;
%let iter_global=;
%macro inner;
%local iter_local;
%global iter_global;
%if (&iter_local eq ) %then %let iter_local=1;
%if (&iter_global eq ) %then %let iter_global=1;
%let iter_local=%eval(&iter_local + (&__iter__ * 5)); %* not retained across outer loops ;
%let iter_global=%eval((&iter_global*2)+(&__iter__)); %* retained across outer loops since it is global ;
%put &sysmacroname &__iter__ &word iter_local=&iter_local iter_global=&iter_global;
%* let __iter__=999; %* if uncommented, this would cause a logic error ;
%mend;
%loop(OUTER_A OUTER_B OUTER_C OUTER_D OUTER_E,mname=outer)
Do NOT reset __iter__ in any inner macro or it will affect the outer macro.
You can *REFERENCE* &__iter__, but do not *CHANGE* its value.
If you need to make logic decisions, copy &__iter__ to a local inner variable.
Also, do NOT use %yourmacro(parameter=&word) syntax in nested calls to
%loop. For example, this will not work:
options mlogic;
%macro level1(firstvar=&word);
%put firstvar in Level1: &firstvar;
%loop(Variable2, mname=level2())
%mend level1;
%macro level2(secondvar=&word);
%put secondvar in level2: &secondvar;
%put INCORRECT: firstvar in Level2: &firstvar;
%mend level2;
%loop(Variable1, mname=level1())
In level1, firstvar is actually assigned the literal text '&word'
(without quotes of course). Inside the level1 macro, this text then
resolves to the value of &word AT THAT TIME. Once the level2 macro
executes, it changes the value of &word, which then changes further
references to &firstvar.
Instead, assign firstvar and secondvar to the value of &word INSIDE
each macro, as described for __iter__ above.
Closely review the mlogic output above for more details.
Instead, code this as follows:
options nomlogic;
%macro level1;
%local firstvar;
%let firstvar=&word;
%put &sysmacroname: firstvar in Level1: &firstvar;
%loop(Variable4 Variable5, mname=level2)
%mend level1;
%macro level2;
%local secondvar;
%let secondvar=&word;
%put &sysmacroname: secondvar in level2: &secondvar;
%put &sysmacroname: CORRECT: firstvar in Level2: &firstvar;
%mend level2;
%loop(Variable1 Variable2 Variable3, mname=level1)
-----------------------------------------------------------------------
Notes:
The nested macro "%code" must be created at run time before calling
this macro.
Use the macro variable "&word" within your %code macro for each token
(word) in the input list.
If your macro has any parameters (named or keyword) (even an empty
list), then specify the macro name with empty parentheses for the mname
parameter or tokenization errors will occur during macro execution. For
example, %loop(hello world,mname=mymacro());. See examples in the
Usage section above.
If your input list has embedded blanks, specify a different dlm value.
Do not use the prefix __ (two underscores) for any macro variables in
the child macro. This prefix is reserved for any macro variables in
this looping macro.
To "carry forward" the value of the iterator across loops, assign it
(&__iter__) to a global macro variable in an inner macro.
---------------------------------------------------------------------*/
%macro loop
/*---------------------------------------------------------------------
Invoke the nested macro "%code" over a list of space separated
list of items.
---------------------------------------------------------------------*/
(__LIST__ /* Space or character separated list of items (REQ) */
,DLM=%str( ) /* Delimiter character (REQ). Default is a space. */
,MNAME=code /* Macro name (Optional). Default is "%code" */
);
%local macro parmerr __iter__ word;
%let macro = &sysmacroname;
%* check input parameters ;
%parmv(MNAME, _req=1,_words=0,_case=N)
%if (&parmerr) %then %goto quit;
%let __iter__ = 1;
%let word = %qscan(%superq(__list__),&__iter__,%superq(dlm));
%do %while (%superq(word) ne %str());
%let word=%unquote(&word);
%&mname /* do not indent macro call */
%let __iter__ = %eval(&__iter__+1);
%let word = %qscan(%superq(__list__),&__iter__,%superq(dlm));
%end;
%quit:
%mend;
/******* END OF FILE *******/
/*=====================================================================
Program Name : parmv.sas
Purpose : Macro parameter validation utility.
Returns parmerr=1 and writes ERROR message
to log for parameters with invalid values.
SAS Version : SAS 8.2
Input Data : N/A
Output Data : N/A
Macros Called : None
Originally Written by : Tom Hoffman
Date : 09SEP1996
Program Version # : 1.0
=======================================================================
Modification History :
Programmer : Tom Hoffman
Date : 16MAR1998
Change/reason : Replaced QTRIM autocall macro with QSYSFUNC
and TRIM in order to avoid conflict with the
i command line macro.
Program Version # : 1.1
Programmer : Tom Hoffman
Date : 04OCT1999
Change/reason : Added _val=NONNEGATIVE. Converted _val=0 1 to
map N NO F FALSE OFF --> 0 and Y YES T TRUE
ON --> 1. Added _varchk parameter to support
variables assumed to be defined before macro
invocation.
Program Version # : 1.2
Programmer : Tom Hoffman
Date : 12APR00
Change/reason : Changed the word 'parameter' in the message
text to 'macro variable' when _varchk=1.
Fixed NONNEGATIVE option.
Program Version # : 1.3
Programmer : Tom Hoffman
Date : 10JUN01
Change/reason : Added _DEF parameter. Returned S_MSG global
macro variable.
Program Version # : 1.4
=====================================================================*/
/*---------------------------------------------------------------------
Usage:
%macro test;
%local macro parmerr;
%let macro = TEST;
%parmv(INTERVAL, _req=1,_words=1)
%parmv(IVAR, _req=1)
%if (%length(&visit) > 7) %then
%parmv(IVAR, _msg=SAS name containing 7 or less characters)
;
%parmv(LZERO, _req=1)
%parmv(UZERO, _req=1)
%parmv(HIGH, _req=1,_val=0 1)
%parmv(DAY, _req=1)
%parmv(PRINT, _req=1,_val=0 1)
%if (&parmerr) %then %goto quit;
....
%quit:
%mend test;
-----------------------------------------------------------------------
Notes:
=======================================================================
This code was developed by HOFFMAN CONSULTING as part of a FREEWARE
macro tool set.
Note: I have received permission from Tom Hoffman to use this macro.
Scott Bass
=======================================================================
The calling macro requires two local variables, PARMERR and MACRO,
where MACRO's value equals the name of the calling macro.
Invoke macro %parmv once for each macro parameter. After the last
invocation branch to the macro's end whenever PARMERR equals 1 (e.g.,
%if (&parmerr) %then %goto quit;).
Macro %parmv can be disabled (except for changing case) by setting the
global macro variable S_PARMV to 0.
Macros using the %parmv tool may not have any parameters in common
with parmv parameters.
Use the _MSG parameter to set parmerr to 1 and issue a message based on
validation criteria within the calling program.
Note that for efficiency reasons, parmv does not validate its own
parameters. Only code valid values for the _REQ, _WORDS, _CASE, and
_VARCHK parameters.
For macros that require 'many' non-parameter macro variables that may
not be defined by the programming environment, consider using the
CHKMVARS macro rather than setting _varchk=1. Both methods will work,
but note that DICTIONARY.MACROS is opened each time that parmv is
invoked with _varchk=1.
--------------------------------------------------------------------*/
%macro parmv
/*---------------------------------------------------------------------
Macro parameter validation utility. Returns parmerr=1 and writes
ERROR message to log for parameters with invalid values.
---------------------------------------------------------------------*/
(_PARM /* Macro parameter name (REQ) */
,_VAL= /* List of valid values or POSITIVE for any positive */
/* integer or NONNEGATIVE for any non-negative integer. */
/* When _val=0 1, OFF N NO F FALSE and ON Y YES T TRUE */
/* (case insensitive) are acceptable aliases for 0 and 1 */
/* respectively. */
,_REQ=0 /* Value required? 0=No, 1=Yes. */
,_WORDS=0 /* Multiple values allowed? 0=No ,1=Yes */
,_CASE=U /* Convert case of parameter value & _val? U=upper, */
/* L=lower,N=no conversion. */
,_MSG= /* When specified, set parmerr to 1 and writes _msg as the*/
/* last error message. */
,_VARCHK=0 /* 0=Assume that variable defined by _parm exists. */
/* 1=Check for existence - issue global statement if not */
/* defined (and _req=0). */
,_DEF= /* Default parameter value when not assigned by calling */
/* macro. */
);
%local _word _n _vl _pl _ml _error _parm_mv;
%global s_parmv s_msg; /* in case not in global environment */
%*---------------------------------------------------------------------
Initialize error flags, and valid (vl) flag.
----------------------------------------------------------------------;
%if (&parmerr = ) %then %do;
%let parmerr = 0;
%let s_msg = ;
%end;
%let _error = 0;
%*---------------------------------------------------------------------
Support undefined values of the _PARM macro variable.
----------------------------------------------------------------------;
%if (&_varchk) %then %do;
%let _parm_mv = macro variable;
%if ^%symexist(&_parm) %then %do;
%if (&_req) %then
%local &_parm
;
%else
%global &_parm
; ;
%end;
%end;
%else %let _parm_mv = parameter;
%*---------------------------------------------------------------------
Get lengths of _val, _msg, and _parm to use as numeric switches.
----------------------------------------------------------------------;
%let _vl = %length(&_val);
%let _ml = %length(&_msg);
%if %length(&&&_parm) %then
%let _pl = %length(%qsysfunc(trim(&&&_parm)));
%else %if %length(&_def) %then %do;
%let _pl = %length(&_def);
%let &&_parm = &_def;
%end;
%else %let _pl = 0;
%*---------------------------------------------------------------------
When _MSG is not specified, change case of the parameter and valid
values conditional on the value of the _CASE parameter.
----------------------------------------------------------------------;
%if ^(&_ml) %then %do;
%let _parm = %upcase(&_parm);
%let _case = %upcase(&_case);
%if (&_case = U) %then %do;
%let &_parm = %qupcase(&&&_parm);
%let _val = %qupcase(&_val);
%end;
%else %if (&_case = L) %then %do;
%if (&_pl) %then %let &_parm = %qsysfunc(lowcase(&&&_parm));
%if (&_vl) %then %let _val = %qsysfunc(lowcase(&_val));
%end;
%else %let _val = %quote(&_val);
%*---------------------------------------------------------------------
When _val=0 1, map supported aliases into 0 or 1.
----------------------------------------------------------------------;
%if (&_val = 0 1) %then %do;
%let _val=%quote(0 (or OFF NO N FALSE F) 1 (or ON YES Y TRUE T));
%if %index(%str( OFF NO N FALSE F ),%str( &&&_parm )) %then
%let &_parm = 0;
%else %if %index(%str( ON YES Y TRUE T ),%str( &&&_parm )) %then
%let &_parm = 1;
%end;
%end;
%*---------------------------------------------------------------------
Bail out when no parameter validation is requested
----------------------------------------------------------------------;
%if (&s_parmv = 0) %then %goto quit;
%*---------------------------------------------------------------------
Error processing - parameter value not null
Error 1: Invalid value - not a positive integer
Error 2: Invalid value - not in valid list
Error 3: Single value only
Error 4: Value required.
Error 5: _MSG specified
----------------------------------------------------------------------;
%if (&_ml) %then %let _error = 5;
%*---------------------------------------------------------------------
Macro variable specified by _PARM is not null.
----------------------------------------------------------------------;
%else %if (&_pl) %then %do;
%*---------------------------------------------------------------------
Loop through possible list of words in the _PARM macro variable.
-----------------------------------------------------------------------;
%if ((&_vl) | ^(&_words)) %then %do;
%let _n = 1;
%let _word = %qscan(&&&_parm,1,%str( ));
%*---------------------------------------------------------------------
Check against valid list for each word in macro parameter
----------------------------------------------------------------------;
%do %while (%length(&_word));
%*---------------------------------------------------------------------
Positive integer check.
----------------------------------------------------------------------;
%if (&_val = POSITIVE) %then %do;
%if %sysfunc(verify(&_word,0123456789)) %then
%let _error = 1;
%else %if ^(&_word) %then %let _error = 1;
%end;
%*---------------------------------------------------------------------
Non-negative integer check.
----------------------------------------------------------------------;
%else %if (&_val = NONNEGATIVE) %then %do;
%if %sysfunc(verify(&_word,0123456789)) %then
%let _error = 1;
%end;
%*---------------------------------------------------------------------
Check against valid list. Note blank padding.
----------------------------------------------------------------------;
%else %if (&_vl) %then %do;
%if ^%index(%str( &_val ),%str( &_word )) %then
%let _error = 2;
%end;
%*---------------------------------------------------------------------
Get next word from parameter value
-----------------------------------------------------------------------;
%let _n = %eval(&_n + 1);
%let _word = %qscan(&&&_parm,&_n,%str( ));
%end; %* for each word in parameter value;
%*---------------------------------------------------------------------
Check for multiple _words. Set error flag if not allowed.
----------------------------------------------------------------------;
%if (&_n ^= 2) & ^(&_words) %then %let _error = 3;
%end; %* valid not null ;
%end; %* parameter value not null ;
%*---------------------------------------------------------------------
Error processing - Parameter value null
Error 4: Value required.
----------------------------------------------------------------------;
%else %if (&_req) %then %let _error = 4;
%*---------------------------------------------------------------------
Write error messages
----------------------------------------------------------------------;
%if (&_error) %then %do;
%let parmerr = 1;
%put %str( );
%put ERROR: Macro %upcase(¯o) user error.;
%if (&_error = 1) %then %do;
%put ERROR: &&&_parm is not a valid value for the &_parm &_parm_mv..;
%put ERROR: Only positive integers are allowed.;
%let _vl = 0;
%end;
%else %if (&_error = 2) %then
%put ERROR: &&&_parm is not a valid value for the &_parm &_parm_mv..;
%else %if (&_error = 3) %then %do;
%put ERROR: &&&_parm is not a valid value for the &_parm &_parm_mv..;
%put ERROR: The &_parm &_parm_mv may not have multiple values.;
%end;
%else %if (&_error = 4) %then
%put ERROR: A value for the &_parm &_parm_mv is required.;
%else %if (&_error = 5) %then %do;
%if (&_parm ^= ) %then
%put ERROR: &&&_parm is not a valid value for the &_parm &_parm_mv..;
%put ERROR: &_msg..;
%end;
%if (&_vl) %then
%put ERROR: Allowable values are: &_val..;
%if %length(&_msg) %then %let s_msg = &_msg;
%else %let s_msg = Problem with %upcase(¯o) parameter values - see LOG for details.;
%end; %* errors ;
%quit:
%*---------------------------------------------------------------------
Unquote the the parameter value, unless it contains an ampersand or
percent sign.
----------------------------------------------------------------------;
%if ^%sysfunc(indexc(&&&_parm,%str(&%%))) %then
%let &_parm = %unquote(&&&_parm);
%mend;
/******* END OF FILE *******/
And here is the working code:
options mprint;
* start with a clean slate ;
proc datasets lib=work kill nowarn nolist;
quit;
* create dummy datasets ;
data foo bar blah;
x=1;
run;
* use the dictionary tables to get a list of datasets in the desired library ;
proc sql noprint;
select memname into :datasets separated by " "
from dictionary.tables
where libname="WORK"
;
quit;
* check the results ;
%put &datasets;
* code fragment to create passwords ;
%macro code;
modify &word (read=rpw write=wpw alter=apw);
run;
%mend;
* use loop macro to execute code fragment for each "word", in this case dataset name ;
proc datasets lib=work;
%loop(&datasets)
quit;
You can gen the needed code simply from SASHELP.VMEMBER with a data step and execute it with %INC.
Modify the WHERE statement to point to your LIBNAME and relevant MEMTYPEs.
filename FT56F001 temp;
data _null_;
file FT56F001;
set sashelp.vmember(keep=memname libname memtype);
by libname;
where memtype eq 'DATA' and libname eq 'SASUSER';
if first.libname then put 'Proc Datasets lib=' libname ';';
put +3 'modify ' memname '(read=pw write=pw alter=pw);';
if last.libname then put +3 'Run; Quit;';
run;
%inc FT56F001;
Great! Looks like this is what I was looking for. I will mark it as correct when I test out the code !
Thanks!
Sid.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: