CODEWITHSUNDEEP
sas
Betty
Betty

Reputation: 37

How do I eliminate variables with missing results in SAS?

Here are my results. Since PPD has a missing result I'd like to eliminate all results for PPD. I.e. I'd like to eliminate all records where ticker='PPD' if any record where ticker='PPD' has a missing result (corr). How can I program this in SAS? I don't want to just eliminate that missing observation but eliminate PPD altogether. Thanks.

Ticker Day Corr

PPD 7 -1

PPD 8

PTP 7 0.547561231

PTP 8 0.183279038

Upvotes: 2

Views: 109

Answers (2)

Joe
Joe

Reputation: 63424

Lots of ways to do this, and what is most efficient depends on your data. If you don't have too much data, then I'd use the easiest method that fits with your knowledge and other habits.

*SQL delete;
proc sql;
  delete from have H where exists (
     select 1 from have V where H.ticker=V.ticker and V.corr is null);
quit;

*FREQ for missing (or means or whatever) then delete from that;
*Requires have to be sorted.;
proc freq data=have;
 tables ticker*corr/missing out=ismiss(where=(missing(corr)));
run;

data want;
 merge have(in=_h) ismiss(in=_m);
 by ticker;
 if _h and not _m;
run;


*double DoW.  Requires either dataset is sorted by ticker,;
*or requires it to be organized by ticker (but tickers can be not alphabetically sorted); *and use norsorted on by statement;
data want;
  do _n_=1 by 1 until (last.ticker);
    set have;
    by ticker;
    if missing(corr) then _miss=1;
  end;
  do _n_=1 by 1 until (last.ticker);
    set have;
    by ticker;
    if _miss ne 1 then output;
  end;
run;

Upvotes: 2

Chris J
Chris J

Reputation: 7769

This is easily accomplished in PROC SQL...

proc sql ;
  create table to_delete as
  select distinct ticker
  from mydata
  where missing(corr) ;

  delete from mydata
  where ticker in(select ticker from to_delete) ;
quit ;

Unfortunately, it can't be done in a single SQL step as the delete from statement would recursively reference the source dataset.

Upvotes: 1

Related Questions