Add column to table sas

Add a new column labeled as “Sale Price” for the whole dataset, the sale is 5% off from original price

/*Question 6*/ proc sql; alter table market add price num label = 'Sale Price'; update Sale Price; set Sale Price = price*.05; select item, branch, id, price, amount from market; quit;
 1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 68 69 /*Question 6*/ 70 proc sql; 71 alter table market 72 add price num label = 'Sale Price'; NOTE: Table WORK.MARKET has been modified, with 5 columns. 73 update Sale Price; _ 79 76 ERROR 79-322: Expecting a SET. ERROR 76-322: Syntax error, statement will be ignored. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 74 set Sale Price = price*.05; ____ 22 76 ERROR 22-322: Syntax error, expecting one of the following: MODE, TRANSACTION. ERROR 76-322: Syntax error, statement will be ignored. 75 select item, branch, id, price, amount 76 from market; NOTE: Statement not executed due to NOEXEC option. 77 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 5989.71k OS Memory 33192.00k Timestamp 10/28/2022 08:59:23 PM Step Count 1603 Switch Count 0 Page Faults 0 Page Reclaims 112 Page Swaps 0 Voluntary Context Switches 0 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 264 78 79 80 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 90
1 ACCEPTED SOLUTION
Accepted Solutions Super User Re: Adding a new column SAS SQL Posted 10-28-2022 06:47 PM (1027 views) | In reply to JoshuaG 
proc sql; create table market as select *, price-price*0.05 as sale_price label='Sale Price' format=dollar10.2 from market; quit;

Add your format in line with the variable creation.

I would recommend you look at the documentation examples to see how SAS SQL works.

5 REPLIES 5 Super User Re: Adding a new column SAS SQL Posted 10-28-2022 05:31 PM (1087 views) | In reply to JoshuaG

As a starting point, the variable label is Sale Price, the variable name is price. You need to refer to it as price (I would rename the column sale_price personally as it sounds like you already have a column named price.

add price num label = 'Sale Price';

See this worked example:

 data class; set sashelp.class; run; proc sql; alter table class add Year2 num label = 'Weight in Year 2'; update class set Year2 = weight * 1.05; quit;

I get the add/modify steps but SAS recreates it anyways so why not use a CREATE TABLE instead?

proc sql; create table market as select *, price*0.05 as sale_price label='Sale Price' from market; quit;
STAT 325 HOMEWORK 6 sql Data description: Kroger warehouse, which has two hubs, supplies 5 supermarkets in local San Diego. You may want to analyze the sale and logistic record of 2014 by using “proc sql” in SAS. market.csv: Column 1: Item ID; Column 2: Item Name; Column 3: Unit Price; Column 4: Sale Amount; Column 5: Store Branch; warehouse.csv: Column 1: Item ID; Column 2: Item Name; Column 3: Warehouse Hub Name; Column 4: Store Branch;

Question

Add a new column labeled as “Sale Price” for the whole dataset, the sale is 5% off from original price

Code

/*Question 6*/ proc sql; alter table market add price num label = 'Sale Price'; update Sale Price; set Sale Price = price*.05; select item, branch, id, price, amount from market; quit;
 1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 68 69 /*Question 6*/ 70 proc sql; 71 alter table market 72 add price num label = 'Sale Price'; NOTE: Table WORK.MARKET has been modified, with 5 columns. 73 update Sale Price; _ 79 76 ERROR 79-322: Expecting a SET. ERROR 76-322: Syntax error, statement will be ignored. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 74 set Sale Price = price*.05; ____ 22 76 ERROR 22-322: Syntax error, expecting one of the following: MODE, TRANSACTION. ERROR 76-322: Syntax error, statement will be ignored. 75 select item, branch, id, price, amount 76 from market; NOTE: Statement not executed due to NOEXEC option. 77 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 5989.71k OS Memory 33192.00k Timestamp 10/28/2022 08:59:23 PM Step Count 1603 Switch Count 0 Page Faults 0 Page Reclaims 112 Page Swaps 0 Voluntary Context Switches 0 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 264 78 79 80 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 90