DATA Step, Macro, Functions and more

Replace prefix of variable for different dataset

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Replace prefix of variable for different dataset

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.


Accepted Solutions
Solution
‎04-17-2018 11:51 AM
Trusted Advisor
Posts: 1,848

Re: Replace prefix of variable for different dataset

Posted in reply to BlaisEcon

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 ;

 

 

View solution in original post


All Replies
Super User
Posts: 23,980

Re: Replace prefix of variable for different dataset

Posted in reply to BlaisEcon

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.


 

Solution
‎04-17-2018 11:51 AM
Trusted Advisor
Posts: 1,848

Re: Replace prefix of variable for different dataset

Posted in reply to BlaisEcon

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 ;

 

 

Trusted Advisor
Posts: 1,848

Re: Replace prefix of variable for different dataset

Pay attention - @Reeza relate to the replacement macro, how to simplify it.
I relate to the second macro.
New Contributor
Posts: 4

Re: Replace prefix of variable for different dataset

[ Edited ]

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?

Super User
Posts: 23,980

Re: Replace prefix of variable for different dataset

Posted in reply to BlaisEcon

Do you have the data set open in a viewer?

New Contributor
Posts: 4

Re: Replace prefix of variable for different dataset

no
Super User
Posts: 10,848

Re: Replace prefix of variable for different dataset

Posted in reply to BlaisEcon
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)
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 127 views
  • 1 like
  • 4 in conversation