BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BlaisEcon
Calcite | Level 5

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 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18

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

7 REPLIES 7
Reeza
Super User

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.


 

Shmuel
Garnet | Level 18

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 ;

 

 

Shmuel
Garnet | Level 18
Pay attention - @Reeza relate to the replacement macro, how to simplify it.
I relate to the second macro.
BlaisEcon
Calcite | Level 5

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?

Reeza
Super User

Do you have the data set open in a viewer?

Ksharp
Super User
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)

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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