topic Re: Get the minus value by multiplying amt column in SAS Programming

Get the minus value by multiplying amt column

JJP1 — Tue, 19 Nov 2019 13:13:49 GMT

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

Re: Get the minus value by multiplying amt column

PeterClemmensen — Tue, 19 Nov 2019 13:13:54 GMT

can you please post your entire PROC SQL Step?

Re: Get the minus value by multiplying amt column

PaigeMiller — Tue, 19 Nov 2019 13:17:05 GMT

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

Re: Get the minus value by multiplying amt column

JJP1 — Tue, 19 Nov 2019 13:20:26 GMT

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;

Re: Get the minus value by multiplying amt column

JJP1 — Tue, 19 Nov 2019 13:22:35 GMT

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

Re: Get the minus value by multiplying amt column

PaigeMiller — Tue, 19 Nov 2019 13:23:37 GMT

These should produce the same results.

Re: Get the minus value by multiplying amt column

Kurt_Bremser — Tue, 19 Nov 2019 13:29:43 GMT

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;

Re: Get the minus value by multiplying amt column

JJP1 — Tue, 19 Nov 2019 13:29:57 GMT

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

Re: Get the minus value by multiplying amt column

JJP1 — Tue, 19 Nov 2019 13:33:02 GMT

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

Re: Get the minus value by multiplying amt column

PaigeMiller — Tue, 19 Nov 2019 13:34:41 GMT

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

Re: Get the minus value by multiplying amt column

PeterClemmensen — Tue, 19 Nov 2019 13:34:25 GMT

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;

Re: Get the minus value by multiplying amt column

JJP1 — Tue, 19 Nov 2019 14:02:59 GMT

Thanks @PeterClemmensen and @Kurt_Bremser & @PaigeMiller .

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