BookmarkSubscribeRSS Feed
Babloo
Rhodochrosite | Level 12

I'm creating one variable called 'VBHTO' in target dataset DI studio and it was coming from the variable AMT from source dataset. Now I was asked to convert any positive values from the variable  'VBHTO' to negative value. e.g. if VBHTO is 40 then should be converted to -40. 

 

I've added the following condition under 'Expression' earlier. Format of this variable is 18.2.

 

case when CLAIM_AMT_TYPE_CD='2'  and SUBTYPE_CD='2' then AMT
end

Now when I modify the same condition as below to incorporate the new changes, I was not seeing any changes in the output. Appreciate if someone of you help me with the solution and to tackle this issue. 

 

case when CLAIM_AMT_TYPE_CD='2'  and SUBTYPE_CD='2' then AMT=-AMT
end

 

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Thats not how an SQL statement is made.  You may want to read up on how to create SQL statements, as that is a basic mistake using SAS syntax in SQL:

case when claim_amt_type_cd='2' and subtype_cd='2' then -amt else amt end as amt

I.e. 


case
 when <condition or value> then <value> when <condition or value> then <value>
else <value>
end as <variable>

 

Forum seems broken again, can't use two code blocks!


Edit (KB): I managed to put your second example into a code window; might be your browser?

Babloo
Rhodochrosite | Level 12

It's not working. I used the following code in expression, and the job was successful but it failed to convert it to negative values.

 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

No code provided.  No test data provided.  "It's not working" - tells us nothing.  This does work:

data have;
  a=3;
run;

data want;
  set have;
  b=-a;
run;

So what is it your doing which is not working?

Patrick
Opal | Level 21

@Babloo wrote:

It's not working. I used the following code in expression, and the job was successful but it failed to convert it to negative values.

 

 


@Babloo

Then most likely your condition is not working as you believe and it got never TRUE. If you just want to test that the condition works then use something like 1=1 as condition and then test what you get for AMT.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 1839 views
  • 1 like
  • 3 in conversation