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

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

Kody_devl — Fri, 17 Jun 2016 13:33:59 GMT

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

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

RW9 — Fri, 17 Jun 2016 13:51:02 GMT

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.

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

Kody_devl — Fri, 17 Jun 2016 14:57:40 GMT

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;

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

LinusH — Fri, 17 Jun 2016 15:40:49 GMT

Odd. Any message in the log? Are all variables numeric?

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

Kody_devl — Fri, 17 Jun 2016 15:45:45 GMT

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

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

RW9 — Fri, 17 Jun 2016 16:23:03 GMT

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-data-AKA-generate/ta-p/258712

Also, post the log of the operation.

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

Kody_devl — Fri, 17 Jun 2016 20:02:58 GMT

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