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

[Nikos]

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

[art297]

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)

[PGStats]

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

[Haikuo]

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

[Linlin]

Hi Haikuo,

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

length _list $ 4000;" should be deleted.

Thanks,

Linlin

[Haikuo]

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

[Linlin]

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