topic Do loop within PROC SQL in SAS Procedures

Do loop within PROC SQL

JediApprentice — Fri, 12 May 2017 15:44:58 GMT

What I would like is to have a do loop within PROC SQL instead of doing it in two steps with PROC SQL and then Data Step:

PROC SQL;
	CREATE TABLE TEST AS 
    SELECT Quantity,
	       Price,
           sum(DEC) AS total_DEC,
	       sum(Ingredient_Cost) AS total_ing_cost,
		   (Calculated total_DEC-Calculated total_ing_cost)/ Calculated total_DEC AS discount,
           (Calculated total_DEC*(1-.82)) format=8.2 AS target_ing_cost,
           (Calculated total_DEC-Calculated target_ing_cost)/ Calculated total_DEC AS target_discount  
      FROM EGTASK.DATA_EXAMPLE;
QUIT;

data TEST_2;
  set TEST;
  do i=.8 to 1.2 by .01;
    target_cost = Price*Quantity*i;
	output;
  end;
run;

What I want is to accomplish what is being done in the data step in the PROC SQL instead (do it in one step). Is this possible?

Re: Do loop within PROC SQL

Tom — Fri, 12 May 2017 16:02:57 GMT

Why not just make the list of values as a separate table and join with it?

data list_of_values;
  do i=.8 to 1.2 by .01;
    output;
  end;
run;

proc sql;
create table TEST as 
  select a.*
       , a.price*a.quantity*b.I as target_cost
  from (
    select Quantity
         , Price
         , sum(DEC) as total_DEC
         , sum(Ingredient_Cost) as total_ing_cost
         , (Calculated total_DEC-Calculated total_ing_cost)/ Calculated total_DEC as discount
         , (Calculated total_DEC*(1-.82)) format=8.2 as target_ing_cost
         , (Calculated total_DEC-Calculated target_ing_cost)/ Calculated total_DEC as target_discount
    from EGTasK.DATA_EXAMPLE 
        ) a
     , list_of_values b
;
quit;

Re: Do loop within PROC SQL

ChrisHemedinger — Fri, 12 May 2017 16:16:06 GMT

Tom beat me to it! "Do this thing for each of these values" -- in SQL, you express that via a join.