Desktop productivity for business analysts and programmers

Filter multiple columns with 1 criteria

Reply
Occasional Contributor
Posts: 11

Filter multiple columns with 1 criteria

Hi

I have the below table. I need to get an output where it shows only Customers who have values between +10 and -10 in each column. The aim is to identify customers who fall within this criteria and export the list for campaigns.  Eg. Customer Code 800 should return column 1 and 2 since it falls within the bracket. It should ignore other columns. I have about 89 columns but have showed only 5 as an example.

CustCode10821152115411771178
31 -260
190 210-2656
228 137
372 -782 -945
451 -153
590 -851-286-403-110
623 -280-4335
649 -355
670 -107-2062-2705
692 -403-373-1312
767 -858
778 -394-671
800-8242122-1393
832 -651-1071
876 80

Could someone please assist me with this

Thanks

Trusted Advisor
Posts: 1,228

Re: Filter multiple columns with 1 criteria

Hi,

How about if one customer meets the criteria and other doesn’t in the same column?

Occasional Contributor
Posts: 11

Re: Filter multiple columns with 1 criteria

Customers who don't fit the criteria can be omitted.

Eg.

Input

CustCode10821152115411771178
31 -260
190 210-2656
228 137
372 -782 -945
451 -153
590 -851-286-403-110
623 -280-4335
649 -355
670 -107-2062-2705
692 -403-373-1312
767 -858
778 -394-671
800-8242122-1393
832 -651-1071
876 80
8991016

Output should be

CustCode10821152115411771178
800-82
8991016
Trusted Advisor
Posts: 1,228

Re: Filter multiple columns with 1 criteria

data have;
input CustCode a b c d e;
datalines;
31 . . 54 4 6
190 7 -8 . 210 -2656
;

data want(drop=i);
set have;
array col(*) a b c d e;
do i=1 to dim(col);
if col{i}<-10 or col{i}>10 then col{i}=.;
end;
run;

Super User
Posts: 9,865

Re: Filter multiple columns with 1 criteria

data have;
input CustCode a b c d e;
datalines;
31 . . 54 4 6 
190 7 -8 . 210 -2656
;
 
data temp(keep=CustCode vname value);
set have;
array col(*) a b c d e;
length vname $ 40;
do i=1 to dim(col);
if -10 le col{i} le 10 then do;vname=vname(col{i}); value=col{i};output;end;
end;
run;
proc transpose data=temp out=want(drop=_:);
by CustCode;
id vname;
var value;
run;

Xia Keshan

Super Contributor
Posts: 339

Re: Filter multiple columns with 1 criteria

Try:
Data Have;
  Input CustCode V1082 V1152 V1154 V1177 V1178;
  Datalines;
31 . . . -260 .
190 . . . 210 -2656
228 . . . 137 .
372 . -782 . -945 .
451 . . . -153 .
590 . -851 -286 -403 -110
623 . . -280 -4335 .
649 . . . -355 .
670 . . -107 -2062 -2705
692 . -403 -373 -1312 .
767 . . . -858 .
778 . -394 -671 . .
800 -8 2 42 122 -1393
832 . . -651 -1071 .
876 . . . 80 .
899 . . 10 1 6
;
Run;

Proc SQL;
  Create Table Want As Select * From Have
  Where (Case When V1082 between -10 and 10 OR
                   V1152 between -10 and 10 OR
             V1154 between -10 and 10 OR
             V1177 between -10 and 10 OR
             V1178 between -10 and 10 Then 1
        Else 0 End)=1;
Quit;

Ask a Question
Discussion stats
  • 5 replies
  • 430 views
  • 2 likes
  • 4 in conversation