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

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

Nikos — Wed, 21 Mar 2012 15:31:39 GMT

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

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

art297 — Wed, 21 Mar 2012 15:46:20 GMT

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)

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

PGStats — Wed, 21 Mar 2012 15:51:34 GMT

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;

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

Haikuo — Wed, 21 Mar 2012 15:57:37 GMT

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

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

Linlin — Wed, 21 Mar 2012 16:37:29 GMT

Hi Haikuo,

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

length _list $ 4000;" should be deleted.

Thanks,

Linlin

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

Haikuo — Wed, 21 Mar 2012 16:40:01 GMT

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

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

Linlin — Wed, 21 Mar 2012 19:02:51 GMT

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