topic Re: how to create variable in sql? in SAS Programming

how to create variable in sql?

233 — Sun, 05 Aug 2018 01:18:14 GMT

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

Re: how to create variable in sql?

ChrisNZ — Sun, 05 Aug 2018 01:50:11 GMT

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

Re: how to create variable in sql?

233 — Sun, 05 Aug 2018 01:57:01 GMT

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?

Re: how to create variable in sql?

SASKiwi — Sun, 05 Aug 2018 02:46:16 GMT

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.

Re: how to create variable in sql?

SASKiwi — Sun, 05 Aug 2018 02:57:06 GMT

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.

Re: how to create variable in sql?

233 — Sun, 05 Aug 2018 03:05:51 GMT

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!

Re: how to create variable in sql?

SASKiwi — Sun, 05 Aug 2018 03:25:44 GMT

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.