I got a lot of dataset which each variable start by a letter. By example :
Dataset 1: Avar1 Avar2 Avar3 ..... Avark
Dataset2: Bvar1 Bvar2 Bvar3 .... Bvarn
And I would like them to become :
Dataset 1: Vvar Vvar2 Vvar3 ..... Vvark
Dataset2: Vvar1 Vvar2 Vvar3 .... Vvarn
I found a macro who could do the job for only one dataset :
` %macro replaceprefix(lib,dsn,start,end,oldprefix,newprefix);
proc contents data=&lib..&dsn. ;
title 'before renaming';
run;
data temp;
set &lib..&dsn.;
run;
%LET ds=%SYSFUNC(OPEN(temp,i));
%let ol=%length(&oldprefix.);
%do i=&start %to &end;
%let dsvn&i=%SYSFUNC(VARNAME(&ds,&i));
%let l=%length(&&dsvn&i);
%let vn&i=&newprefix.%SUBSTR(&&dsvn&i,&ol+1,%EVAL(&l-&ol));
%end;
data &lib..&dsn.;
set temp;
%do i=&start %to &end;
&&vn&i=&&dsvn&i;
drop &&dsvn&i;
%end;
%let rc=%SYSFUNC(CLOSE(&ds));
proc contents data=&lib..&dsn.;
title 'Replacing Prefix on Selected variables ';
run;
%mend replaceprefix;
After, I tried to automate this macro for all my dataset. I created the following Macro:
%macro test(end,ele,letter);
%do k=1 %TO &end;
%Let i= %scan(&ele,&k);
%Let j=%scan(&letter,&k);
%let nvar= %sysfunc(attrnd(&i,nvars));
%replaceprefix(WORK,&i,1,&nvar,&j,v);
data &i; set &i; van=&k; run; %end;
%mend ;
I received the following error code :
ERROR: The ATTRND function referenced in the %SYSFUNC or %QSYSFUNC macro function is not found. ERROR: %EVAL function has no expression to evaluate, or %IF statement has no condition.
ERROR: The %TO value of the %DO M loop is invalid.
ERROR: The macro REPLACEPREFIX will stop executing. ERROR: The ATTRND function referenced in the %SYSFUNC or %QSYSFUNC macro function is not found.
Any idea why ?
Thank's everyone.
1) There is no ATTRND function. You probaly meant to ATTRN.
2) The syntax to ATTRN function is: ATTRN(dsid, <attribute>)
You enter NVAR as attribute - that is OK.
To get the dataset ID (dsid) you need add a line:
%let dsid = %sysfunc(open(<dataset_name>));
I assume that &i - in your code - contains the dsn (dataset name)
Your code shoul be - try it -
%macro test(end,ele,letter);
%do k=1 %TO &end;
%Let i= %scan(&ele,&k); /* dataset name ? */
%Let j=%scan(&letter,&k);
%let dsid = %sysfunc(open(&i)); /* dataset id */
%let nvar= %sysfunc(attrnd(&dsid,nvars));
%let dsid = close(&dsid); /* resume dataset */
%replaceprefix(WORK,&i,1,&nvar,&j,v);
data &i; set &i; van=&k; run;
%end;
%mend ;
If the naming convention is well structured, and yours appear to be you can do a mass rename at once, assuming all your indexes are numeric.
rename avar1-avar200 = VVAR1-VVAR200;
rename bvar1-bvar200 = vvar1-vvar200;
@BlaisEcon wrote:
I got a lot of dataset which each variable start by a letter. By example :
Dataset 1: Avar1 Avar2 Avar3 ..... Avark
Dataset2: Bvar1 Bvar2 Bvar3 .... Bvarn
And I would like them to become :
Dataset 1: Vvar Vvar2 Vvar3 ..... Vvark
Dataset2: Vvar1 Vvar2 Vvar3 .... Vvarn
I found a macro who could do the job for only one dataset :
` %macro replaceprefix(lib,dsn,start,end,oldprefix,newprefix); proc contents data=&lib..&dsn. ; title 'before renaming'; run; data temp; set &lib..&dsn.; run; %LET ds=%SYSFUNC(OPEN(temp,i)); %let ol=%length(&oldprefix.); %do i=&start %to &end; %let dsvn&i=%SYSFUNC(VARNAME(&ds,&i)); %let l=%length(&&dsvn&i); %let vn&i=&newprefix.%SUBSTR(&&dsvn&i,&ol+1,%EVAL(&l-&ol)); %end; data &lib..&dsn.; set temp; %do i=&start %to &end; &&vn&i=&&dsvn&i; drop &&dsvn&i; %end; %let rc=%SYSFUNC(CLOSE(&ds)); proc contents data=&lib..&dsn.; title 'Replacing Prefix on Selected variables '; run;
%mend replaceprefix;
After, I tried to automate this macro for all my dataset. I created the following Macro:%macro test(end,ele,letter); %do k=1 %TO &end; %Let i= %scan(&ele,&k); %Let j=%scan(&letter,&k); %let nvar= %sysfunc(attrnd(&i,nvars)); %replaceprefix(WORK,&i,1,&nvar,&j,v); data &i; set &i; van=&k; run; %end; %mend ;
I received the following error code :
ERROR: The ATTRND function referenced in the %SYSFUNC or %QSYSFUNC macro function is not found. ERROR: %EVAL function has no expression to evaluate, or %IF statement has no condition.
ERROR: The %TO value of the %DO M loop is invalid.
ERROR: The macro REPLACEPREFIX will stop executing. ERROR: The ATTRND function referenced in the %SYSFUNC or %QSYSFUNC macro function is not found.
Any idea why ?
Thank's everyone.
1) There is no ATTRND function. You probaly meant to ATTRN.
2) The syntax to ATTRN function is: ATTRN(dsid, <attribute>)
You enter NVAR as attribute - that is OK.
To get the dataset ID (dsid) you need add a line:
%let dsid = %sysfunc(open(<dataset_name>));
I assume that &i - in your code - contains the dsn (dataset name)
Your code shoul be - try it -
%macro test(end,ele,letter);
%do k=1 %TO &end;
%Let i= %scan(&ele,&k); /* dataset name ? */
%Let j=%scan(&letter,&k);
%let dsid = %sysfunc(open(&i)); /* dataset id */
%let nvar= %sysfunc(attrnd(&dsid,nvars));
%let dsid = close(&dsid); /* resume dataset */
%replaceprefix(WORK,&i,1,&nvar,&j,v);
data &i; set &i; van=&k; run;
%end;
%mend ;
Thank you for your answer.
However,
When I used your code I get : : "You cannot open WORK.Mere1.DATA for output access with member-level control because
WORK.Mere1.DATA is in use by you in resource environment DMS Process."
do you have any idea why?
Do you have the data set open in a viewer?
data dataset1;
input Avar1 Avar2 Avar3;
cards;
1 2 3
;
run;
%macro rename(table=);
proc transpose data=&table(obs=0) out=temp;
var _all_;
run;
data _null_;
set temp end=last;
if _n_=1 then call execute("proc datasets library=work nolist nodetails;modify &table ;rename ");
call execute(catt(_name_,'=V',substr(_name_,2)));
if last then call execute(';quit;');
run;
%mend;
%rename(table=dataset1)
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 25. 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.