[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sas-l
Subject:    SAS Forum: Drop columns that contain at leats one of these values 1, 0 or missing
From:       Roger DeAngelis <rogerjdeangelis () GMAIL ! COM>
Date:       2017-09-29 16:35:16
Message-ID: 1810813053461306.WA.rogerjdeangelisgmail.com () listserv ! uga ! edu
[Download RAW message or body]

SAS Forum: Drop columns that contain at leats one of these values 1, 0 or missing

Probably best done with a matrix language? (one liner)
Unfortunately SAS 'whichn' only picks up the first column wiht the 'drop' value.

  TWO SOLUTIONS

  1 IML/R WPS/PROC R  (elegant suspect is as easy in IML)

      have<-have[ , colSums((have==1 | have==0 | is.na(have)==1)) == 0];

  2 SAS (Not happy but tried whichn)

     DOSUBL (COMPILE TIME)

           length vardrp $4096;
           retain vardrp;

           set sd1.have end=dne;
           array nums[*] _numeric_;

           do col=1 to dim(nums);
              if nums[col] in (.,0,1) then do;
                 nam=vname(nums[col]);
                 if indexw(vardrp,nam," ")=0 then vardrp=catx(" ",vardrp,nam);
              end;
           end;
           if dne then call symputx("vardrp",vardrp);

     MAINLINE

            set sd1.have(drop=&vardrp.);

see
https://goo.gl/Grdqqd
https://communities.sas.com/t5/Base-SAS-Programming/Need-to-drop-null-columns-starts-with-or-prefixed-with-a-certain/m-p/399744




HAVE
====

 SD1.HAVE total obs=6                                                    |  RULES
                                                                         |
                     ATTRIB_    ATTRIB_    ATTRIB_    ATTRIB_    ATTRIB_ |  Drop \
ltr2-ltr5 because Obs     CATEGORY       ITR1       ITR2       ITR3       ITR4       \
ITR5  |  at least on of (0,1,.)  |
 1     #ofpeople        20         30         10         0          .    |
 2     #ofprovide       10          0          1         .          .    |
 3     #ofclaims        40         25          3         .          .    |
 4     AmountBill       50        100          1         1          .    |
 5     AmountPaid       11         35          4         .          .    |
 6     AmountDed         5          6          5         .          .    |


WANT
====

The WPS System

 WORK.WANT total obs=6

                       ATTRIB_
  Obs     CATEGORY       ITR1

   1     #ofpeople        20
   2     #ofprovide       10
   3     #ofclaims        40
   4     AmountBill       50
   5     AmountPaid       11
   6     AmountDed         5


*                _               _       _
 _ __ ___   __ _| | _____     __| | __ _| |_ __ _
> '_ ` _ \ / _` | |/ / _ \   / _` |/ _` | __/ _` |
> > > > > > (_| |   <  __/  | (_| | (_| | || (_| |
> _| |_| |_|\__,_|_|\_\___|   \__,_|\__,_|\__\__,_|

;


options validvarname=upcase;
libname sd1 "d:/sd1";
data sd1.have;
infile cards4 missover;
input category :$10. Attrib_Itr1 Attrib_Itr2 Attrib_Itr3 Attrib_Itr4  Attrib_Itr5;
cards4;
#ofpeople     20       30  10 0
#ofproviders  10       0    1
#ofclaims     40       25   3
AmountBilled  50       100  1 1
AmountPaid    11       35   4
AmountDed     5        6    5
;;;;
run;quit;

*_        ______  ____    ______
\ \      / /  _ \/ ___|  / /  _ \
 \ \ /\ / /| |_) \___ \ / /| |_) |
  \ V  V / |  __/ ___) / / |  _ <
   \_/\_/  |_|   |____/_/  |_| \_\

;


%utl_submit_wps64('
libname sd1 "d:/sd1";
options set=R_HOME "C:/Program Files/R/R-3.4.0";
libname wrk "%sysfunc(pathname(work))";
proc r;
submit;
source("c:/Program Files/R/R-3.4.0/etc/Rprofile.site",echo=T);
library(haven);
have<-read_sas("d:/sd1/have.sas7bdat");
have<-have[ , colSums((have==1 | have==0 | is.na(have)==1)) == 0];
endsubmit;
import r=have data=wrk.wantwps;
run;quit;
');

proc print data=wantwps;
run;quit;


[prev in list] [next in list] [prev in thread] [next in thread]