topic Re: Deleting rows from datasets within a library in SAS Programming

Deleting rows from datasets within a library

nduksy — Tue, 07 Dec 2021 21:14:47 GMT

I want to delete all rows where the if statement is true for all datasets within a specific library.

Below is what I have done thus far, the problem with this is that it fuses all the datasets into one. I want to still keep the datasets, but just delete all rows that meet the condition

data temp;
	set keep2.S:;
	if f1 =. and f2 =. then delete;
run;

PS: Every dataset in the library starts with S

Re: Deleting rows from datasets within a library

SASKiwi — Tue, 07 Dec 2021 22:51:23 GMT

How about this? Sounds like you have an Excel file with some columns that aren't "empty".

data have;
  a = 1; b = 2; f1 = 3; f2 = 4;
  output;
   a = 1; b = 2; f1 = .; f2 = .;
  output;
run;

proc sql;
create table members as 
select libname, memname
from dictionary.members
where libname = "WORK" and memtype = "DATA"
;
quit;

data _null_;
  set members;
  where libname = 'WORK';
  call execute ('data ' !! strip(libname) !! '.' !! memname !!
                '; set ' !! strip(libname) !! '.' !! memname !!'; if f1 = . and f2 = . then delete; run;'); 
run;

Re: Deleting rows from datasets within a library

nduksy — Wed, 08 Dec 2021 01:11:52 GMT

Thanks for your input. I have tried to substitute my values with your solution and I haven't gotten far.

proc sql;
create table members as 
select libname, memname
from dictionary.members
where libname = "keep2" and memtype = "DATA"
;
quit;

data _null_;
  set members;
  where libname = 'keep2';
  call execute ('data ' !! strip(libname) !! '.' !! memname !!
                '; set ' !! strip(libname) !! '.' !! memname !!'; if f1 = . and f2= . then delete; run;'); 
run;

ps: The name of the library that contains the datasets is called keep2. There are 50 datasets in the library and 23 of meet that condition (f1 and f2 are blank)

Re: Deleting rows from datasets within a library

SASKiwi — Wed, 08 Dec 2021 01:43:25 GMT

LIBNAMES need to be uppercase:

where libname = "KEEP2" and memtype = "DATA"

And remove the WHERE LIBNAME statement in this step:

data _null_;
  set members;
  where libname = 'keep2'; * <== remove this;
  call execute ('data ' !! strip(libname) !! '.' !! memname !!
                '; set ' !! strip(libname) !! '.' !! memname !!'; if f1 = . and f2= . then delete; run;'); 
run;

Re: Deleting rows from datasets within a library

nduksy — Wed, 08 Dec 2021 03:17:51 GMT

Thank you very much. It got the job done.