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

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.

Thank you for your help, have a fabulous and productive day! I am a novice today, but someday when I accumulate enough knowledge, I can help others in my capacity.
1 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18

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;

 

 

 

View solution in original post

24 REPLIES 24
PeterClemmensen
Tourmaline | Level 20

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

Phil_NZ
Barite | Level 11

Hi @PeterClemmensen 

 

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

 

Thank you!

Thank you for your help, have a fabulous and productive day! I am a novice today, but someday when I accumulate enough knowledge, I can help others in my capacity.
Reeza
Super User

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

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
Garnet | Level 18

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
Barite | Level 11

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,

Thank you for your help, have a fabulous and productive day! I am a novice today, but someday when I accumulate enough knowledge, I can help others in my capacity.
Shmuel
Garnet | Level 18

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
Barite | Level 11

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:

My97_0-1613197223954.png

 

Could you please help me to figure out my fallacy?

 

 

 

Warmest regards.

 

 

 

Thank you for your help, have a fabulous and productive day! I am a novice today, but someday when I accumulate enough knowledge, I can help others in my capacity.
Shmuel
Garnet | Level 18

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
Barite | Level 11

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!

Thank you for your help, have a fabulous and productive day! I am a novice today, but someday when I accumulate enough knowledge, I can help others in my capacity.
Shmuel
Garnet | Level 18

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
Barite | Level 11

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.

Thank you for your help, have a fabulous and productive day! I am a novice today, but someday when I accumulate enough knowledge, I can help others in my capacity.
Shmuel
Garnet | Level 18

Have you assigned a LIBNAME to that library ?

libname _9B  '<path>/_9b';

then chack again the sashelp.vtable.

Phil_NZ
Barite | Level 11

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!

Thank you for your help, have a fabulous and productive day! I am a novice today, but someday when I accumulate enough knowledge, I can help others in my capacity.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 24 replies
  • 1143 views
  • 7 likes
  • 6 in conversation