topic Re: Replace prefix of variable for different dataset in SAS Programming

Replace prefix of variable for different dataset

BlaisEcon — Thu, 12 Apr 2018 18:57:20 GMT

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.

Re: Replace prefix of variable for different dataset

Reeza — Thu, 12 Apr 2018 19:46:11 GMT

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.

Re: Replace prefix of variable for different dataset

Shmuel — Thu, 12 Apr 2018 19:46:29 GMT

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 ;

Re: Replace prefix of variable for different dataset

Shmuel — Thu, 12 Apr 2018 19:49:52 GMT

Pay attention - @Reeza relate to the replacement macro, how to simplify it.
I relate to the second macro.

Re: Replace prefix of variable for different dataset

Ksharp — Fri, 13 Apr 2018 14:04:25 GMT

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)

Re: Replace prefix of variable for different dataset

BlaisEcon — Tue, 17 Apr 2018 13:48:17 GMT

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?

Re: Replace prefix of variable for different dataset

Reeza — Tue, 17 Apr 2018 14:41:18 GMT

Do you have the data set open in a viewer?

Re: Replace prefix of variable for different dataset

BlaisEcon — Tue, 17 Apr 2018 14:41:46 GMT