data discountRet_kinson2 discountCat_kinson2 discountInt_kinson2;
set work.orders;
by Customer_ID Order_Type;
if first.Order_Type then Total_Sales=0;
Total_Sales+Total_Retail_Price;
if last.Order_Type and Total_Sales >= 200 then do;
select (Order_Type);
when (1) output discountRet_kinson2;
when (2) output discountCat_kinson2;
when (3) output discountInt_kinson2;
end;
end;
I have the above code and I have to express them using SQL instead of data step.
I have try a few lines of code, but I don't know how to express the if subset statement in SQL.
Is there any similar procedure in SQL that I ignored?
my code:
proc sql;
create table work.discountRet_sliu116
work.discountCat_sliu116
work.discountInt_sliu116 as
select Customer_ID, Customer_Name, Total_Sales
from hw7.orders2018
My homework requires me to do that.
I think I didn't make enough clarification. In the data step, a if subset statement is using to create a variable called total_sales (seems like accumulate the value of a original variable called retail_price)
And now I have to code them using SQL, so I was wondering is there anyway I could do in SQL to create a variable and assign the accumulation of the values from the existed variable?
You can create only one table per SQL query so it is going to take 3 SQL queries to get what you want.
IMO this is a dumb homework question, as a DATA step is the best method for doing BY-type processing and creating multiple tables in the one step.
proc sql;
create table work.discountRet_sliu116 as
select Customer_ID
,Customer_Name
,sum(Total_Retail_Price) as Total_Sales
from hw7.orders2018
where Order_type = 1
group by Customer_ID
,Customer_Name
having sum(Total_Retail_Price) >= 200
;
quit;
Something like the above should work for one order type, then just repeat for the other two.
Thank you so much! I am not very familiar with SQL and I am looking for notes of sub query but still stuck on the problem. Really appreciate your help!
No problem. I don't think you need sub-queries in this case, not unless there is more to the problem than you have posted.
All I'm using is a GROUP BY to summarise the data by customer ID.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.