SAS Macro to add a prefix to some or all variables in a data set...

Often we need to add a prefix to some or all variables in a dataset before we might have to merge datasets that have similar column attributes…This macro would allow you to do that….

Try it for yourself….

view source

print?

/**

SAS Macro to add a prefix to some or all variables in a data set...

to be used like this...

%prefixvars(inpdsn,prefix,outdsn,excludevars=);

inpdsn - input dataset name libname.dsnname

prefix - prefix that you want to assign

outdsn - output dataset name libname.dsnname

excludevars - vars that you do not want to rename with the prefix

**/

%macro prefixvars(inpdsn,prefix,outdsn,excludevars=);

/* split the excludevars into individual macro var names for later use*/

%let num=1;

%let excludevar=%scan(%upcase(&excludevars),&num,' ');

%let excludevar&num=&excludevar;

%do %while(&excludevar ne );

%let num=%eval(&num + 1);

%let excludevar=%scan(&excludevars,&num,' ');

%let excludevar&num=&excludevar;

%end;

%let numkeyvars=%eval(&num - 1); /* this is number of variables given in the exclude vars */

%let dsid=%sysfunc(open(&inpdsn));   /* open the dataset and get the handle */

%let numvars=%sysfunc(attrn(&dsid,nvars)); /* get the number of variables */

data &outdsn;

set &inpdsn(rename=(

/*rename all the variables that are not in the excludevars= */

%do i = 1 %to &numvars;

%let flag=N;

%let var&i=%sysfunc(varname(&dsid,&i));

%do j=1 %to &numkeyvars;

%if %upcase(&&var&i) eq &&excludevar&j %then %let flag=Y;

%end;

%if &flag eq N %then %do; &&var&i=&prefix&&var&i  %end;

%end;));

%let rc=%sysfunc(close(&dsid));

run;

%mend prefixvars;

/*Call the macro now*/

%prefixvars(sashelp.buy,fr_,work.out,excludevars=date)