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.
It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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