## Filter multiple columns with 1 criteria

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.

 CustCode 1082 1152 1154 1177 1178 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

Could someone please assist me with this

Thanks

Posts: 1,270

## Re: Filter multiple columns with 1 criteria

Posted in reply to manojdawson

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

 CustCode 1082 1152 1154 1177 1178 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

Output should be

 CustCode 1082 1152 1154 1177 1178 800 -8 2 899 10 1 6
Posts: 1,270

## Re: Filter multiple columns with 1 criteria

Posted in reply to manojdawson

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: 10,849

## Re: Filter multiple columns with 1 criteria

Posted in reply to manojdawson
```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: 355

## Re: Filter multiple columns with 1 criteria

Posted in reply to manojdawson

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;

Discussion stats
• 5 replies
• 598 views
• 2 likes
• 4 in conversation