CODEWITHSUNDEEP
sqlsas
Jarod
Jarod

Reputation: 11

Creating a new column in a table using proc sql, need to multiply four other columns together

I'm creating a new table to count total billing by state. Total billing includes price, quantity sold, a discount per customer, and sales tax in each state. I need a new table that gives me the 50 states and the total billing in each state.

I've tried simply multiplying the 4 fields together, but that hasn't seemed to work. 

proc sql;
create table jarod.total bill as
select
    State_CD,
    Sale_ID,
    PriceOUT*(1-Discount)*(1+State Sales Tax)
from jarod.sales
    order by State_CD;
quit;

I wanted to get the total sale price for each transaction, which i would then somehow sum by state (unsure how to do this either) but my table won't create due to error.

Upvotes: 1

Views: 1417

Answers (2)

user12211404
user12211404

Reputation:

I believe that the proc freq will help you, give it a try ^^

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I think you want an aggregation query:

proc sql;
create table jarod.total_bill as
    select State_CD, sum(PriceOUT*(1-Discount)*(1+State Sales Tax)) as total_billing
    from jarod.sales
    group by State_CD;
quit;

Upvotes: 1

Related Questions