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)
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.