topic Re: Case When for Multiple Values in a Group in SAS Enterprise Guide

Case When for Multiple Values in a Group

arjessup — Mon, 06 May 2019 17:09:59 GMT

This seems like it would be simple, but I have had no luck making it work.

I have a set of numbers representing account numbers. I need to assign each number to one of three groups. For simplicity, let's use numbers:

111

222

333

444

555

666

777

888

999

The groups will belong to ABC, DEF or GHI. The group names are not in the dataset. How/can I write this using 1 next CASE/WHEN statement? If so, how?

Re: Case When for Multiple Values in a Group

Reeza — Mon, 06 May 2019 17:16:27 GMT

Are there any rules for assigning to groups? What happens if you have a number of groups that's not divisible by 3, ie if you have 10 account numbers.

Re: Case When for Multiple Values in a Group

TomKari — Mon, 06 May 2019 18:42:52 GMT

Here's the code I used to create the test data, and the code that is generated by the EG query builder.

Tom

data Have;
	input InNum;
	cards;
111
222
333
444
555
666
777
888
999
run;

PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_HAVE AS 
   SELECT t1.InNum, 
          /* OutNum */
            (CASE
               WHEN (InNum in(111,333,555))
               THEN "ABC"
               WHEN (InNum in(222, 888))
               THEN "DEF"
               ELSE "GHI"
            END) AS OutNum
      FROM WORK.HAVE t1;
QUIT;

Re: Case When for Multiple Values in a Group

arjessup — Tue, 07 May 2019 15:29:38 GMT

No, the groups have various numbers of accounts.

Re: Case When for Multiple Values in a Group

arjessup — Tue, 07 May 2019 15:35:14 GMT

Thank you Tom. I was way over complicating the issue.