DATA Step, Macro, Functions and more

how to create variable in sql?

Reply
Occasional Contributor 233
Occasional Contributor
Posts: 14

how to create variable in sql?

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
Super User
Posts: 2,516

Re: how to create variable in sql?

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

 

Occasional Contributor 233
Occasional Contributor
Posts: 14

Re: how to create variable in sql?

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?

 

Super User
Posts: 4,030

Re: how to create variable in sql?

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.

Super User
Posts: 4,030

Re: how to create variable in sql?

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.

Occasional Contributor 233
Occasional Contributor
Posts: 14

Re: how to create variable in sql?

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!

Super User
Posts: 4,030

Re: how to create variable in sql?

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.

Ask a Question
Discussion stats
  • 6 replies
  • 84 views
  • 0 likes
  • 3 in conversation