Get the minus value by multiplying amt column

[JJP1]

Hi,

iam trying below code but iam getting amt column values as '0.00'.but i need the amt value to be -229 and 229 based on below conditions .

please help where iam going wrong

case when Sign='D'then Amt = (-1 * Amt ) else Amt
else when Sign='C' then Amt =(1 * Amt ) else Amt
For example if amt column holds 299 the if it is D then -299 and if it is C then amt should be 299

Amt

[PeterClemmensen]

can you please post your entire PROC SQL Step?

[JJP1]

Hi @PeterClemmensen ,please find below and kindly help

proc sql;
create view work.W4J4UL3 as
select

(case when sign ='C' then Amt = -1 * Amt
else Amt
end) as Amt length = 8
format = 11.2
informat = 11.2,
(case when Sign1='C'
then Amt1=(-1 * Amt1 )
else Amt1
end) as Amt1 length = 8
format = 11.2
informat = 11.2,

from xxxx
;
quit;

[Kurt_Bremser]

Start with most simple version, and DO NOT write spaghetti code:

data have;
input sign $ amt;
datalines;
D 299
C 299
;

proc sql;
create table want as
select
  sign,
  case
    when sign = 'C' then -amt
    else amt
  end as amt
from have;
quit;

[JJP1]

Thanks @Kurt_Bremser .It is working as expected.i follow as you suggested,thanks

[PeterClemmensen · Accepted solution]

Loose the amt= part in your case statements and the last comma like this

proc sql;
create view work.W4J4UL3 as
select

(case when sign ='C' then -1 * Amt
else Amt
end) as Amt length = 8
format = 11.2
informat = 11.2,
(case when Sign1='C'
then (-1 * Amt1 )
else Amt1
end) as Amt1 length = 8
format = 11.2
informat = 11.2

from xxxx
;
quit;

[JJP1]

Thanks @PeterClemmensen and @Kurt_Bremser & @PaigeMiller .

i am getting what i expected in output.Thanks for quickly helping me on this

[PaigeMiller]

The syntax for SQL (I assume this is what you are using, please confirm) does not use the equal sign after THEN

So you want

case when Sign='D' then -Amt
when Sign='C' then Amt end as Amt

[JJP1]

Thanks @PaigeMiller .Actually i should do soemthing like (-1 * Amt).you mean this is same as -Amt please.and can we not apply -1*amt ? please

[PaigeMiller]

These should produce the same results.

[JJP1]

Iam getting 0.00 only please,please have a look on test code i posted on reply to @PeterClemmensen  please

[PaigeMiller]

---

@JJP1 wrote:

Iam getting 0.00 only please,please have a look on test code i posted on reply to @PeterClemmensen  please

---

As I stated, and as I and others have shown by example, you do not want an equal sign after THEN. The presence of the equal sign after THEN means you are testing to see if two things are equal in the THEN clause, which is NOT what you want. (You do want to test things are equal in the WHEN clause, not in the THEN clause)