topic Re: How to delete one column in all files in one library ? in SAS Programming

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

Phil_NZ — Fri, 12 Feb 2021 19:17:55 GMT

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.

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

PeterClemmensen — Fri, 12 Feb 2021 19:19:52 GMT

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

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

ballardw — Fri, 12 Feb 2021 19:25:11 GMT

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.

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

Phil_NZ — Fri, 12 Feb 2021 19:27:19 GMT

Hi @PeterClemmensen

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

Thank you!

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

Reeza — Fri, 12 Feb 2021 21:07:20 GMT

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

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

Shmuel — Fri, 12 Feb 2021 20:47:10 GMT

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;

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

Phil_NZ — Sat, 13 Feb 2021 05:14:08 GMT

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,

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

Shmuel — Sat, 13 Feb 2021 06:02:02 GMT

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

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

Phil_NZ — Sat, 13 Feb 2021 06:20:36 GMT

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.

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

Shmuel — Sat, 13 Feb 2021 06:46:37 GMT

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;

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

Phil_NZ — Sat, 13 Feb 2021 08:02:28 GMT

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!

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

Shmuel — Sat, 13 Feb 2021 08:08:29 GMT

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.

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

Phil_NZ — Sat, 13 Feb 2021 08:17:53 GMT

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.

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

Shmuel — Sat, 13 Feb 2021 08:28:58 GMT

Have you assigned a LIBNAME to that library ?

libname _9B  '<path>/_9b';

then chack again the sashelp.vtable.

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

Phil_NZ — Sat, 13 Feb 2021 08:58:03 GMT

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!

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

Kurt_Bremser — Sat, 13 Feb 2021 09:05:26 GMT

Count the opening vs. closing brackets here:

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

You need an additional closing bracket in the string.

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

Phil_NZ — Sat, 13 Feb 2021 09:28:35 GMT

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.

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

Shmuel — Sat, 13 Feb 2021 09:41:13 GMT

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

line should be:

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

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

Phil_NZ — Sat, 13 Feb 2021 10:05:55 GMT

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.

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

Kurt_Bremser — Sat, 13 Feb 2021 11:45:33 GMT

Details

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