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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.