BookmarkSubscribeRSS Feed
Nikos
Fluorite | Level 6

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

6 REPLIES 6
art297
Opal | Level 21

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
Opal | Level 21

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
Haikuo
Onyx | Level 15

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
Lapis Lazuli | Level 10

Hi Haikuo,

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

length _list $ 4000;" should be deleted.

Thanks,

Linlin

Haikuo
Onyx | Level 15

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

Linlin
Lapis Lazuli | Level 10

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 1051 views
  • 2 likes
  • 5 in conversation