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

[Phil_NZ]

Hi SAS Users,

I have a library named "_9b", this library contains 64 files sas7bdat. All of these 64 files have the same variables, and the lengths of variables are not consistent. I am wondering if you can help me to generate the code to delete one column named "BDATE" through all 64 files in this library( I want to delete this one because it is both numeric and character and useless in my dataset).

If the information is not enough or I present not clear, please let me know.

Many thanks and warm regards.

[PeterClemmensen]

Do you want to delete the variable for all SAS data sets in the library?

[Phil_NZ]

Hi @PeterClemmensen 

I want to delete the variable "BDATE" through all dataset in this library.

Thank you!

[Reeza]

1. Identify which datasets have the column (sashelp.vcolumns)
2. Use PROC DATASETS or PROC SQL via CALL EXECUTE to drop them.

Conceptually this could be done in a single data step

[ballardw]

Really sounds like the data type issue should be corrected.

Or probably even investigate how the data is brought into SAS with inconsistent results and prevent that from happening in the future. Especially if you have multiple variables that ending up with different lengths.

This a symptom of relying on Proc Import which makes guesses for ever file and leads to a number of data issues like you describe.

[Shmuel]

the code to delete a variable from a data set is:

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

wrap the code with a macro:

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

finally run next code:

data _null_;
  set sashelp.vtable(where=(libname='_9B';
       cmd = cats( '%delvar(', strip(libname), strip(memname), '<var 2 del>');
       call execute(cmd);
       put _N_= memname= ' was submitted to run' ;
run;

[Phil_NZ]

Hi @Shmuel 

I adjusted and applied this code and it does not work properly, can you please have a look?

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 = cats( '%delvar(', strip(libname), strip(memname), 'BDATE');
       call execute(cmd);
       put _N_= memname= ' was submitted to run' ;
run;

/*******************************/

However, the result turns out like that:

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: Macro parameter contains syntax error.
_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.
ERROR: Macro parameter contains syntax error.
_N_=2 memname=AUSTRALIA_MERGE2  was submitted to run
NOTE: Data file _9B.AUSTRIA_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.

I tried to find an answer from another post but still do not know what is really going on, because I never faced such a problem when using SAS EG in my computer

Many thanks and cheers,

[Shmuel]

Sorry, my fault. CATS function need be replaced by CATX:

cmd = catx(',', '%delvar(', strip(libname), strip(memname), 'BDATE'||');'); 
put cmd=; /* put to log for easy debug */

[Phil_NZ]

Hi @Shmuel 

Thank you for your insightful idea, however, when I run the whole code as suggested:

options mprint;
Libname  _9b'C:\Users\pnguyen\OneDrive - Massey University\PhD JOURNEY\2Bfiltering' access=readonly;
/*******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');
       call execute(cmd);
       put _N_= memname= ' was submitted to run' ;
run;

The log when running such a code is as below:

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');
44                call execute(cmd);
45                put _N_= memname= ' was submitted to run' ;
46         run;

NOTE: There were 0 observations read from the data set SASHELP.VTABLE.
      WHERE libname='_9b';

It is clear that "NOTE: There were 0 observations read from the data set SASHELP.VTABLE. WHERE libname='_9b';" is an issue.

My directory path is as below:

Could you please help me to figure out my fallacy?

Warmest regards.

[Shmuel]

you did not copy the whole line with the CATX function.

I have added 2 arguments :

   (1) ',' at the beginning - this is the delimiter between macro arguments

   (2) ');' as last argument to close the macro %delvar line

I added also a "put cmd=;" to enable easy check of submitted line by call execute;

[Phil_NZ]

Hi @Shmuel Thank you for your update, but I still face the same problem, is there any solution then?

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

/*******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;

Log:

 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: There were 0 observations read from the data set SASHELP.VTABLE.
      WHERE libname='_9b';

Many thanks!

[Shmuel]

As much as I remember the data in sashelp.vtable is in uppercase.

You can brows this dataset and check whether I'm right.

I coded "where libname='_9B'"  with capital letter, while you ran with "_9b" in lowcase.

[Phil_NZ]

Hi @Shmuel 

Thank you for your dedicated help so far. Actually, I tried both lowercase and uppercase but it not yet worked. 

28         options mprint;
29         Libname  _9b'C:\Users\pnguyen\OneDrive - Massey University\PhD JOURNEY\2Bfiltering' access=readonly;
NOTE: Libref _9B refers to the same physical library as SAS.
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: There were 0 observations read from the data set SASHELP.VTABLE.
      WHERE libname='_9B';

Warm regards.

[Shmuel]

Have you assigned a LIBNAME to that library ?

libname _9B  '<path>/_9b';

then chack again the sashelp.vtable.

[Phil_NZ]

Hi @Shmuel !

Thank you

The code is that

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


/*******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;

And the log is that, now the error is quite strange to me, because it is clear that there are three parameters in the macro.

28         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;

cmd=%delvar(,_9B,ARGENTINA_MERGE2_,BDATE);
ERROR: More positional parameters found than defined.
_N_=1 memname=ARGENTINA_MERGE2_  was submitted to run
cmd=%delvar(,_9B,AUSTRALIA_MERGE2_,BDATE);
ERROR: More positional parameters found than defined.
_N_=2 memname=AUSTRALIA_MERGE2_  was submitted to run
2                                                          The SAS System                          21:44 Saturday, February 13, 2021

cmd=%delvar(,_9B,AUSTRIA_MERGE2_,BDATE);
ERROR: More positional parameters found than defined.
_N_=3 memname=AUSTRIA_MERGE2_  was submitted to run
cmd=%delvar(,_9B,BELGIUM_MERGE2_,BDATE);
ERROR: More positional parameters found than defined.
_N_=4 memname=BELGIUM_MERGE2_  was submitted to run
cmd=%delvar(,_9B,BRAZIL_MERGE2_,BDATE);
ERROR: More positional parameters found than defined.
_N_=5 memname=BRAZIL_MERGE2_  was submitted to run
cmd=%delvar(,_9B,BULGARIA_MERGE2_,BDATE);
ERROR: More positional parameters found than defined.
_N_=6 memname=BULGARIA_MERGE2_  was submitted to run
cmd=%delvar(,_9B,CHILE_MERGE2_,BDATE);
ERROR: More positional parameters found than defined.
_N_=7 memname=CHILE_MERGE2_  was submitted to run
cmd=%delvar(,_9B,CHINA_MERGE2_,BDATE);
ERROR: More positional parameters found than defined.
_N_=8 memname=CHINA_MERGE2_  was submitted to run
cmd=%delvar(,_9B,COLOMBIA_MERGE2_,BDATE);
ERROR: More positional parameters found than defined.

Many thanks!