topic Re: Create new variable from a value of multiples column in SAS Programming

Create new variable from a value of multiples column

ndamo — Sun, 30 Aug 2020 15:10:31 GMT

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

Re: Create new variable from a value of multiples column

PaigeMiller — Sun, 30 Aug 2020 15:32:27 GMT

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;

Re: Create new variable from a value of multiples column

Shmuel — Sun, 30 Aug 2020 15:35:07 GMT

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.

Re: Create new variable from a value of multiples column

ndamo — Sun, 30 Aug 2020 15:51:11 GMT

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"

Re: Create new variable from a value of multiples column

PaigeMiller — Sun, 30 Aug 2020 15:57:49 GMT

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;

Re: Create new variable from a value of multiples column

ndamo — Sun, 30 Aug 2020 16:17:10 GMT

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 .

Re: Create new variable from a value of multiples column

Shmuel — Sun, 30 Aug 2020 16:28:16 GMT

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;

Re: Create new variable from a value of multiples column

ndamo — Sun, 30 Aug 2020 16:39:15 GMT

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

Re: Create new variable from a value of multiples column

PaigeMiller — Sun, 30 Aug 2020 18:24:33 GMT

@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.

Re: Create new variable from a value of multiples column

mkeintz — Sun, 30 Aug 2020 23:34:26 GMT

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".

Re: Create new variable from a value of multiples column

PaigeMiller — Sun, 30 Aug 2020 22:48:19 GMT

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.

Re: Create new variable from a value of multiples column

ndamo — Mon, 31 Aug 2020 11:33:39 GMT

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.