Help using Base SAS procedures

How to Delete a SAS dataset based on the values of one of the same dataset variables

Reply
Contributor
Posts: 68

How to Delete a SAS dataset based on the values of one of the same dataset variables

Hi all,

I have a SAS dataset that I would like to delete when all the values of one of its variables are all missing.

Any suggestions would be more than welcomed.

Thank you in advance

Kind regards

Nikos

PROC Star
Posts: 7,471

Re: How to Delete a SAS dataset based on the values of one of the same dataset variables

Here is a SAS-L thread that addresses finding out if a variable only contains missing values. http://www.listserv.uga.edu/cgi-bin/wa?A2=ind1112B&L=sas-l&P=R15676&D=1&H=0&J=0&O=D&T=1&m=333745

Once you identify that the condition exists, deleteing the file can be accomplished in various ways (e.g., proc delete)

Respected Advisor
Posts: 4,920

How to Delete a SAS dataset based on the values of one of the same dataset variables

Use this macro. ds is the dataset name, nVar is the variable name

%macro delMiss(ds, mVar);

proc sql noprint;

select sum(&mVar is not missing) into :nMissNb from &ds;

%if &nMissNb = 0 %then drop table &ds.;;

quit;

%mend;

PG

PG
Respected Advisor
Posts: 3,156

Re: How to Delete a SAS dataset based on the values of one of the same dataset variables

Base on Ksharp 's code (sorry I can't find it right now), you can also try:

proc sql noprint;

select catx(' ','nmiss(',name,') as',name) into : list separated by ','

  from dictionary.columns

   where libname='WORK' and memname='HAVE';

select count(*) into : nobs from have;

create table temp as

  select &list from have;

quit;

data _null_;

set temp;

length _list $ 4000;

array _x{*} _numeric_;

do i=1 to dim(_x);

  if _x{i} eq &nobs then

    do;

    call symputx('drop','have');

stop;

end;

run;

proc sql;

drop table &drop;

quit;

The code above can be easily translated into Macros.

Regards,

Haikuo

Super Contributor
Posts: 1,636

Re: How to Delete a SAS dataset based on the values of one of the same dataset variables

Hi Haikuo,

I think there should be an "end" after "stop", and "

length _list $ 4000;" should be deleted.

Thanks,

Linlin

Respected Advisor
Posts: 3,156

Re: How to Delete a SAS dataset based on the values of one of the same dataset variables

Right. Thanks, Linlin, now you know I am a slacker.

Super Contributor
Posts: 1,636

Re: How to Delete a SAS dataset based on the values of one of the same dataset variables

Hi Nikos,

with help of so many people I came up with a macro. This macro will drop your dataset if you have a variable with all missing value. becareful if you are going to use it.

%macro test(lib,dsn);

proc sql noprint;

select catx(' ','nmiss(',name,') as',name) into : list separated by ','

  from dictionary.columns

   where libname="%upcase(&lib)" and memname="%upcase(&dsn)";

select count(*) into : nobs from &lib..&dsn;

create table temp as

  select &list from &lib..&dsn;

quit;

data _null_;

set temp;

array _x{*} _numeric_;

do i=1 to dim(_x);

  if _x{i} eq &nobs then do;

       call execute('proc sql;

    drop table &lib..&dsn;

  quit;');

stop;end;

end;

run;

%mend;

%test(your_library,your_dateset)

Linlin

Ask a Question
Discussion stats
  • 6 replies
  • 274 views
  • 2 likes
  • 5 in conversation