BookmarkSubscribeRSS Feed
manojdawson
Calcite | Level 5

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

5 REPLIES 5
stat_sas
Ammonite | Level 13

Hi,

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

manojdawson
Calcite | Level 5

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
stat_sas
Ammonite | Level 13

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;

Ksharp
Super User
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

user24feb
Barite | Level 11

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;

sas-innovate-2024.png

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.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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