Help using Base SAS procedures

Do loop within PROC SQL

Reply
Frequent Contributor
Posts: 123

Do loop within PROC SQL

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?

Super User
Super User
Posts: 6,500

Re: Do loop within PROC SQL

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;
Community Manager
Posts: 2,761

Re: Do loop within PROC SQL

Tom beat me to it! "Do this thing for each of these values" -- in SQL, you express that via a join.
Ask a Question
Discussion stats
  • 2 replies
  • 210 views
  • 2 likes
  • 3 in conversation