BookmarkSubscribeRSS Feed
JediApprentice
Pyrite | Level 9

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?

2 REPLIES 2
Tom
Super User Tom
Super User

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;
ChrisHemedinger
Community Manager
Tom beat me to it! "Do this thing for each of these values" -- in SQL, you express that via a join.
Register for SAS Innovate 2025!! The premier event for SAS users, May 6-9 in Orlando FL. Sign up now for the best deals!

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 4211 views
  • 2 likes
  • 3 in conversation