topic Re: Filter multiple columns with 1 criteria in SAS Enterprise Guide

Filter multiple columns with 1 criteria

manojdawson — Tue, 18 Nov 2014 02:31:18 GMT

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

Re: Filter multiple columns with 1 criteria

stat_sas — Tue, 18 Nov 2014 03:20:05 GMT

Hi,

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

Re: Filter multiple columns with 1 criteria

manojdawson — Tue, 18 Nov 2014 03:32:09 GMT

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

Re: Filter multiple columns with 1 criteria

stat_sas — Tue, 18 Nov 2014 04:13:29 GMT

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;

Re: Filter multiple columns with 1 criteria

Ksharp — Tue, 18 Nov 2014 13:06:18 GMT

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

Re: Filter multiple columns with 1 criteria

user24feb — Tue, 18 Nov 2014 13:45:55 GMT

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;