Create new variable from a value of multiples column

[ndamo]

I need helps, I am trying to create one variable from more columns as can you see below : 
Class1 Class2 Class3 Class4 Class5 Class6 Class 7
    "1"       "1"       "1"       "1"       "1"       "1"         "1"
     "2"      "2"       "2"       "2"       "2"        "2"       "2"
    "3"       "3"       "3"       "3"       "3"        "3"        "3"

I handled this with "Case function" using PROC SQL, for the Class 1 it sounds work but for Class 2, I have a warning : "WARNING: Variable ALPHA already exists on file WORK.BENELUX".

My goal is to create the column "Alpha" if (Class1 to Class7="1")

PROC SQL;
CREATE TABLE BENELUX
AS

SELECT *,
(CASE WHEN CLASS_L1="1" THEN CLASS_L1 ELSE '' END) AS ALPHA,
(CASE WHEN CLASS_L2="1" THEN CLASS_L2 ELSE '' END) AS ALPHA
/*
CLASS_L3="1" OR
CLASS_L4="1" OR
CLASS_L5="1" OR
CLASS_L6="1" OR
CLASS_L7="1"))*/

FROM BETADSN;

Your help will be welcome!

Thanks advanced

[PaigeMiller]

You can't have the variable ALPHA being created twice, which is what you are doing with the two CASE commands after SELECT *.

This will work, although it may not be what you want. Please show us the desired output.

PROC SQL;
CREATE TABLE BENELUX
AS

SELECT *,
(CASE WHEN CLASS_L1="1" THEN CLASS_L1 ELSE '' END) AS ALPHA1,
(CASE WHEN CLASS_L2="1" THEN CLASS_L2 ELSE '' END) AS ALPHA2
FROM BETADSN;

[Shmuel]

What do you mean by if (Class1 to Class7="1") ?

If you mean all 7 variables class1 to class7 have the same value then the code should be

case (class1=1 and class2=1 and ... and class7=1) then 1 as alpha else
case (class1=2 and ..........................and class7=2) then 2 as alpha
..

otherwise, please post the expected output.

[ndamo]

Thank you, Yes I would like just create the variable ALPHA when class1=1 and class2=1 and class3=1 and class4=1 class5=1 and class6= and class7=1

the expected out will be : 

ALPHA

  "1"

  "1"

  "1"

  "1"

  "1"

  "1"

  "1"

  "1" 

[PaigeMiller]

So if any of CLASS1-CLASS7 is not a "1" then what value do we assign to ALPHA? Your most recent post seems to indicate that you are using the AND operator but your original post indicates you are using the OR operator, these will not produce the same results. Why do you need ALPHA to be character?

This code produces a numeric variable using the AND operator, and will produce a 0 if they are not all "1"

PROC SQL;
CREATE TABLE BENELUX
AS
SELECT *,
(class_l1="1")*(class_l2="1")*(class_l3="1")*(class_l4="1")*(class_l5="1")*(class_l6="1")*(class_l7="1") as alpha
from betadsn;
quit;

[ndamo]

Thank you, in my core data set, the format these variables(Class1 to Class7) is CHAR : 

    "1"="Blue"

    "2"="White"

    "3"="Red"

    "9"="Other"  

If I put 1 or "Blue" in the program , it doesn't work. I need just the first value because I will perform the PROc FREQ for that variable . 

[ndamo]

Thank you! It works but rather than using *, I put OR because "*" doesn't work for char value.

[PaigeMiller · Accepted solution]

---

@ndamo wrote:
Thank you! It works but rather than using *, I put OR because "*" doesn't work for char value.

---

You keep switching between the AND operator and the OR operator. Please stop dragging this out and clear this up. We could also benefit from a clear and complete problem statement, instead of us having to ask question after question.

In the code I provided, the * does work.

[mkeintz]

This is an example of what I think is the (unspoken) primary goal of this web site.

Yes the site has "sas" in its name, and sas coding suggestions that meet the OP's objective do get marked as "solution", but the greatest success is in getting the OP to recognize what is needed to properly define a problem.  That is the basic concept behind my frequent harangues of "help us help you".

[PaigeMiller]

Yes, I agree with @mkeintz , I have no idea what the actual problem is that the original poster wants to solve. I am also not particularly happy that my answer was marked correct, as I don't really think I have solved the problem. I might have moved the conversation forward somewhat, but that's a different thing.

All programming problems require a clear and complete understanding of what the problem is, and we never got that here.

[ndamo]

Sorry if you found something wrong on this exchange/post, it's my first post and I will make better in the next post.

Once I have more than two variables, I am confused about using "AND" and "OR".I tested both because initially, I think "AND" would work but I had not the expected output so I switched in using "OR". Of course, your tips helped me to solve this issue and I replied to you that the Format of my variables (Class 1 -Class) is exactly as:
"1"="blue"
"2"="Red"
"3"="White"
"7"="Other"

I tried with your tips below but I have errors in the log: " *  required numeric value "  and the same  error happened with "||"

PROC SQL;
CREATE TABLE BENELUX
AS
SELECT *,
(class_l1="1")*(class_l2="1")*(class_l3="1")*(class_l4="1")*(class_l5="1")*(class_l6="1")*(class_l7="1") as alpha
from betadsn;
quit;

Finally, the following program works:

PROC SQL;
CREATE TABLE BENELUX
AS
SELECT *,
(class_l1="1") OR (class_l2="1") OR (class_l3="1") OR (class_l4="1") OR (class_l5="1") OR (class_l6="1") OR (class_l7="1") as alpha
from betadsn;
quit;

Thank you for this help and Again Sorry for this misunderstanding.

[Shmuel]

If all rows should result with alpha="1" then the code is"

case (class1=1 and class2=1 and ... and class7=1) or
case (class1=2 and ......................and class7=2) or
case (class1=3 and ......................and class7=3) or
...
case (class1=7 and ......................and class7=7) 
then "1" as alpha;
else "9" as alpha;