topic How to add a calculated column of data into an existing table/dataset in New SAS User

How to add a calculated column of data into an existing table/dataset

bb123 — Wed, 10 Mar 2021 17:53:34 GMT

I have created a table that contains all of the columns/variables that I need into one table from excel sheets. I need to add 1-3 more columns that have calculated values from the values of a few of the variables/columns in the table. I have looked everywhere but nothing is working the way I need it to. for reference, this is the data table I am working with that needs to have an extra column in it.

What I want is to have a final column in the end called "total billing" that includes the calculated values [price * quantity * (1-discount) * (1+tax rate) ] and I thought I could do that at once but in my query builder it does not allow me to type in numbers for the 1-discount and the 1+tax rate, so I think I have to make separate columns for both of those and then combine the 4 columns at the end in the query to make the final computed column. however, I cannot figure out how to make the 1-discount column (want to be called 'discount rate') or the 1+tax rate column (want to be called 'taxed') to be added to this overall table in order to get to the final computed column. Let me know if any more information is needed. For reference, this is what my query builder looks like when I press 'build column'

... i have seen online some videos on how to do a computed column in query builder but none of those look like my SAS 9.4 screen and tool bar in any way.

Re: How to add a calculated column of data into an existing table/dataset

Kurt_Bremser — Wed, 10 Mar 2021 18:05:37 GMT

First of all, please provide your data in usable form, namely in a data step with datalines. We cannot write code for pictures.

Re: How to add a calculated column of data into an existing table/dataset

bb123 — Wed, 10 Mar 2021 18:42:59 GMT

I understand that code cannot be written for pictures, but the dataset is too big to attach here - it won't allow it. I was hoping that someone could put in a reply what the code to do what I am asking for should look like/contain, if that makes sense. for example,

proc ___

....

or if something else needs to be done to describe it.

Re: How to add a calculated column of data into an existing table/dataset

Kurt_Bremser — Wed, 10 Mar 2021 18:51:29 GMT

For developing code, a representative subset is enough (about what's contained in your picture, but usable). That usually needs no more than 50 lines of DATALINES code.

Re: How to add a calculated column of data into an existing table/dataset

bb123 — Wed, 10 Mar 2021 20:46:07 GMT

Here is some of the dataset

Re: How to add a calculated column of data into an existing table/dataset

Reeza — Wed, 10 Mar 2021 21:16:25 GMT

I think you just need the advanced computed column then you can type in a formula and not rely on the builder. It's much easier that way in my opinion.

You could also use a basic data step as follows, assuming you have the 4 variables in your data set. If you don't, create them before between the SET and TOTAL_BILLING statements.

data want;
set have;

total_billing = price*quantity * (1-discount) * (1+ tax_rate);

run;

@bb123 wrote:

I have created a table that contains all of the columns/variables that I need into one table from excel sheets. I need to add 1-3 more columns that have calculated values from the values of a few of the variables/columns in the table. I have looked everywhere but nothing is working the way I need it to. for reference, this is the data table I am working with that needs to have an extra column in it.

What I want is to have a final column in the end called "total billing" that includes the calculated values [price * quantity * (1-discount) * (1+tax rate) ] and I thought I could do that at once but in my query builder it does not allow me to type in numbers for the 1-discount and the 1+tax rate, so I think I have to make separate columns for both of those and then combine the 4 columns at the end in the query to make the final computed column. however, I cannot figure out how to make the 1-discount column (want to be called 'discount rate') or the 1+tax rate column (want to be called 'taxed') to be added to this overall table in order to get to the final computed column. Let me know if any more information is needed. For reference, this is what my query builder looks like when I press 'build column'

... i have seen online some videos on how to do a computed column in query builder but none of those look like my SAS 9.4 screen and tool bar in any way.