BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bb123
Fluorite | Level 6

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.

Screen Shot 2021-03-10 at 12.45.46 PM.png

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'

Screen Shot 2021-03-10 at 12.50.21 PM.png

... 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. 

Screen Shot 2021-03-10 at 12.52.25 PM.png

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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.

Screen Shot 2021-03-10 at 12.45.46 PM.png

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'

Screen Shot 2021-03-10 at 12.50.21 PM.png

... 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. 

Screen Shot 2021-03-10 at 12.52.25 PM.png


 

View solution in original post

5 REPLIES 5
bb123
Fluorite | Level 6

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. 

 

Kurt_Bremser
Super User

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.

bb123
Fluorite | Level 6

Here is some of the dataset

Reeza
Super User

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.

Screen Shot 2021-03-10 at 12.45.46 PM.png

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'

Screen Shot 2021-03-10 at 12.50.21 PM.png

... 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. 

Screen Shot 2021-03-10 at 12.52.25 PM.png


 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 8508 views
  • 1 like
  • 3 in conversation