How do you add a prefix to some or all variables in a
dataset using a SAS macro?
Answers were Sorted based on User's Feedback
Answer / kumar
If it is Interview Question I would do say something like this.
/* Running the renaming macro */
options macrogen mprint mlogic;
%macro rename(lib,dsn);
options pageno=1 nodate;
proc contents data=&lib..&dsn;
title "Before Renaming All Variables";
run;
proc sql noprint;
select nvar into :num_vars
from dictionary.tables
where libname="&LIB" and
memname="&DSN";
select distinct(name) into :var1-
:var%TRIM(%LEFT(&num_vars))
from dictionary.columns
where libname="&LIB" and
memname="&DSN";
quit;
run;
proc datasets library=&LIB;
modify &DSN;
rename
%do i=1 %to &num_vars;
&&var&i=NEWNAME_&&var&i.
%end;
;
quit;
run;
options pageno=1 nodate;
proc contents data=&lib..&dsn;
title "After Renaming All Variables";
run;
%mend rename;
Is This Answer Correct ? | 4 Yes | 0 No |
Answer / sastechies
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....
/**
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)
Is This Answer Correct ? | 3 Yes | 1 No |
Name statements that function at both compile and execution time?
Mention how to limit decimal places for the variable using proc means?
what can you learn from the sas log when debugging? : Sas programming
it will become easy if uuu provide website linkssss and list of consultanciessssss
how do u validate sas program
how to debug and test the sas program? : Sas-administrator
how can you put a "trace" in your program? : Sas programming
what are the methods that you would employ to fine tune your SQL extract process using SAS/Access or Proc SQL?
What is difference between sas rename and lable?
What is the difference between the proc sql and data step?
Are you sensitive to code walk-throughs peer review or QC review?
How could you generate test data with no input data?