BookmarkSubscribeRSS Feed
233
Calcite | Level 5 233
Calcite | Level 5
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
6 REPLIES 6
ChrisNZ
Tourmaline | Level 20

SQL has no notion of row order. Why must you use SQL?

 

233
Calcite | Level 5 233
Calcite | Level 5

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?

 

SASKiwi
PROC Star

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.

SASKiwi
PROC Star
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.

233
Calcite | Level 5 233
Calcite | Level 5

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!

SASKiwi
PROC Star

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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 794 views
  • 0 likes
  • 3 in conversation