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
Hi,
How about if one customer meets the criteria and other doesn’t in the same column?
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 |
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;
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
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.