topic Re: Adding a new column SAS SQL in SAS Programming

Adding a new column SAS SQL

JoshuaG — Fri, 28 Oct 2022 21:02:11 GMT

Background

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;

Log

 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

Re: Adding a new column SAS SQL

Reeza — Fri, 28 Oct 2022 21:33:20 GMT

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;

@JoshuaG wrote:

Background

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;

Log

 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

Re: Adding a new column SAS SQL

JoshuaG — Fri, 28 Oct 2022 21:53:29 GMT

How should I go about adding the dollar format here?

Re: Adding a new column SAS SQL

JoshuaG — Fri, 28 Oct 2022 21:54:34 GMT

I was able to get it to output, need help with the formatting now

Re: Adding a new column SAS SQL

JoshuaG — Fri, 28 Oct 2022 21:57:25 GMT

I tried adding it like this:

/*Question 6*/
proc sql;
create table market as
select *, price-price*0.05 as sale_price label='Sale Price'
from market;
quit;

proc sql;
select item, branch, id, price, amount, sale_price
from market;
format sale_price=Dollar10.2;
quit;

However, it still isnt showing up

Re: Adding a new column SAS SQL

Reeza — Fri, 28 Oct 2022 22:47:17 GMT

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.