DATA Step, Macro, Functions and more

Update a calculated field in the current table from other fields in the same table

Reply
Contributor
Posts: 58

Update a calculated field in the current table from other fields in the same table

Hi,

 

I am a developer who is new to SAS.

 

I have a table, myData that has three columns, BAL1, BAL2, and RUNOFF.

 

How can I easily, via some kind of datastep or proc SQL, update the RUNOFF  = (BAL1-BAL2) (for all of the rows)?

 

There are no more that  50,000 rows / obs in myData.

 

Many Thanks

 

Kody_Devl

 

 

Super User
Super User
Posts: 7,952

Re: Update a calculated field in the current table from other fields in the same table

Posted in reply to Kody_devl

Hi,

 

You may want to run through the SAS free online videos to get a graps of the language if you are new:

data want;
  set have;
  runoff=bal1-bal2;
run;

Assumes of course that all three variables are the same type = numeric.  It doesn't matter the number of observations - much like a cursor in SQL, a datastep is a loop of ever observation in the dataset, so that formula gets applied to each row.

Contributor
Posts: 58

Re: Update a calculated field in the current table from other fields in the same table

It didn't work.

 

The Bal1 and Bal2 and RunOff are all Numeric 8

 

BEFORE UPDATE:

  Bal1           Bal2          RunOff

-154385          0               0

 

AFTER UDPATE;

 

  Bal1           Bal2          RunOff

-154385          0               

 

I then ran a query to Calculate RunOff and the query returned the same results for RunOff, Blank

 

Code Used:

data Work.Data_P2_0;

set Work.Data_P2;

runoff=PremiumWO0-PremiumWO1;

run;

 

Super User
Posts: 5,428

Re: Update a calculated field in the current table from other fields in the same table

Posted in reply to Kody_devl
Odd. Any message in the log? Are all variables numeric?
Data never sleeps
Contributor
Posts: 58

Re: Update a calculated field in the current table from other fields in the same table

No Errors: 0

No Warnings: 0

 

This is why I am asking for help. 

This shouldn't be that hard but somthing is wrong.

Could it be some kind of formatting problem on the numeric 8 field.

 

The Zero fields were created by SQL when the table is created

 

0 as PremiumWO1

0 as RunOff

 

??

Super User
Super User
Posts: 7,952

Re: Update a calculated field in the current table from other fields in the same table

Posted in reply to Kody_devl

Post some test data, follow this post for how to get it:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

 

Also, post the log of the operation.

Contributor
Posts: 58

Re: Update a calculated field in the current table from other fields in the same table

I have imported the code but am having trouble with the correct parameters to get it to work.  I will have to play with it a bit.

 

Thanks

 

Kody_devl

 

 

 

Ask a Question
Discussion stats
  • 6 replies
  • 334 views
  • 0 likes
  • 3 in conversation