How to delete one column in all files in one library ?

[Shmuel]

Sorry, again my fault. There is an excess comma;

line should be:

cmd = catx(',', '%delvar('|| strip(libname), strip(memname), 'BDATE'||');'); put cmd=;

[Phil_NZ]

Hi @Shmuel !

Thank you for your dedicated help

The code is

options mprint;
Libname  _9b'C:\Users\pnguyen\OneDrive - Massey University\PhD JOURNEY\2Bfiltering' access=readonly;

/*proc contents data=_9b._ALL_ out=contents;
run;*/

/*******DROP BDATE**************/
%macro delvar(lib,dsiname,var2del);
     data &lib..&dsname;
       set &lib..&dsname(drop=&var2del);
    run;
%mend;

data _null_;
  set sashelp.vtable(where=(libname='_9B'));
       cmd = catx(',', '%delvar('|| strip(libname), strip(memname), 'BDATE'||');'); put cmd=;
       call execute(cmd);
       put _N_= memname= ' was submitted to run' ;
run;

Now it exists a new error.

 options mprint;
29         Libname  _9b'C:\Users\pnguyen\OneDrive - Massey University\PhD JOURNEY\2Bfiltering' access=readonly;
NOTE: Libref _9B was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: C:\Users\pnguyen\OneDrive - Massey University\PhD JOURNEY\2Bfiltering
30         
31         /*proc contents data=_9b._ALL_ out=contents;
32         run;*/
33         
34         /*******DROP BDATE**************/
35         %macro delvar(lib,dsiname,var2del);
36              data &lib..&dsname;
37                set &lib..&dsname(drop=&var2del);
38             run;
39         %mend;
40         
41         data _null_;
42           set sashelp.vtable(where=(libname='_9B'));
43                cmd = catx(',', '%delvar('|| strip(libname), strip(memname), 'BDATE'||');'); put cmd=;
44                call execute(cmd);
45                put _N_= memname= ' was submitted to run' ;
46         run;

NOTE: Data file _9B.ARGENTINA_MERGE2.DATA is in a format that is native to another host, or the file encoding does not match the 
      session encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce 
      performance.
cmd=%delvar(_9B,ARGENTINA_MERGE2,BDATE);
WARNING: Apparent symbolic reference DSNAME not resolved.
MPRINT(DELVAR):   data _9B.&dsname;
2                                                          The SAS System                          22:58 Saturday, February 13, 2021

WARNING: Apparent symbolic reference DSNAME not resolved.
MPRINT(DELVAR):   set _9B.&dsname(drop=BDATE);
MPRINT(DELVAR):   run;
_N_=1 memname=ARGENTINA_MERGE2  was submitted to run
NOTE: Data file _9B.AUSTRALIA_MERGE2.DATA is in a format that is native to another host, or the file encoding does not match the 
      session encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce 
      performance.

Warmest regards.

[Kurt_Bremser]

Details

Details

Details

Lokk at how the macro parameter is written in the %MACRO statement, and how you try to use it in the macro body.

[Shmuel]

My eyes seems to cheat me. Replace dsiname by dsname all over the code.

[Phil_NZ]

Hi @Shmuel 

It still does not work well even the code running in macro seems to be right

options mprint;
Libname  _9b'C:\Users\pnguyen\OneDrive - Massey University\PhD JOURNEY\2Bfiltering' access=readonly;

/*******DROP BDATE**************/
%macro delvar(lib,dsname,var2del);
     data &lib..&dsname;
       set &lib..&dsname(drop=&var2del);
    run;
%mend;

data _null_;
  set sashelp.vtable(where=(libname='_9B'));
       cmd = catx(',', '%delvar('|| strip(libname), strip(memname), 'BDATE'||');'); put cmd=;
       call execute(cmd);
       put _N_= memname= ' was submitted to run' ;
run;

Log

MPRINT(DELVAR):   data _9B.ARGENTINA_MERGE2;
MPRINT(DELVAR):   set _9B.ARGENTINA_MERGE2(drop=BDATE);
MPRINT(DELVAR):   run;
_N_=1 memname=ARGENTINA_MERGE2  was submitted to run
NOTE: Data file _9B.AUSTRALIA_MERGE2.DATA is in a format that is native to another host, or the file encoding does not match the 
      session encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce 
      performance.

Warm regards.

[Shmuel]

What do you mean by "..not work well" ???
There is nothing in the log to give a hint.

[Phil_NZ]

Hi @Shmuel !

The code:

options mprint;

Libname  _9b'C:\Users\pnguyen\Desktop\New folder2' access=readonly;
/*I just change to one country for printing log purpose*/

/*******DROP BDATE**************/
%macro delvar(lib,dsname,var2del);
     data &lib..&dsname;
       set &lib..&dsname(drop=&var2del);
    run;
%mend;

data _null_;
  set sashelp.vtable(where=(libname='_9B'));
       cmd = catx(',', '%delvar('|| strip(libname), strip(memname), 'BDATE'||');'); put cmd=;
       call execute(cmd);
       put _N_= memname= ' was submitted to run' ;
run;

Sorry I did not post the whole log. The whole log is as below:

MPRINT(DELVAR):   data _9B.ARGENTINA_MERGE2;
MPRINT(DELVAR):   set _9B.ARGENTINA_MERGE2(drop=BDATE);
MPRINT(DELVAR):   run;
_N_=1 memname=ARGENTINA_MERGE2  was submitted to run
NOTE: There were 1 observations read from the data set SASHELP.VTABLE.
      WHERE libname='_9B';
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
      

NOTE: CALL EXECUTE generated line.
1         + data _9B.ARGENTINA_MERGE2;        set _9B.ARGENTINA_MERGE2(drop=BDATE);     run;
NOTE: Data file _9B.ARGENTINA_MERGE2.DATA is in a format that is native to another host, or the file encoding does not match the 
      session encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce 
      performance.

ERROR: Write access to member _9B.ARGENTINA_MERGE2.DATA is denied.
NOTE: The SAS System stopped processing this step because of errors.

And there is no output or result being created.

Many thanks and warm regards.

[Shmuel · Accepted solution]

You defined the libname _9B as READ ONLY.

It means that the output cannot rewrite the input.

This is the reason you got the error message:

ERROR: Write access to member _9B.ARGENTINA_MERGE2.DATA is denied.

you need to decide where to write the output - should it be to WORK or some other library or omit the "read only" from the libname statement.

Adapt next line - the bold red mark part:

           data &lib..&dsname; 

If output library is WORK the this line should bee:

          data work.&dsname;

[Kurt_Bremser]

Count the opening vs. closing brackets here:

cmd = cats( '%delvar(', strip(libname), strip(memname), 'BDATE');

You need an additional closing bracket in the string.

[Phil_NZ]

Thank you @Kurt_Bremser 

I did change such a code based on suggestion of @Shmuel  but the result still be a mystery

cmd = catx(',', '%delvar(', strip(libname), strip(memname), 'BDATE'||');'); put cmd=

Many thanks.

---