Multiplying multiple columns in SQL - 【StackMirror】|sql|sas

Possible Duplicate:
SAS proc SQL and arrays

I am trying to calculate the ratios of multiple columns, multiple times in SAS using SQL. For example, I want:

(((start*start_period)+(middle*middle_period)+(end*end_period))/number_days) AS azmonth;

The problem is that while start_period, middle_period, end_period, and number_days are constant and start, middle, and end are one set of about 7 sets that need to be multiplied in the same way. Is there a way besides typing it out for each of them?

Start_per Mid_per End_per Start1....Start20 Mid1...Mid20 End1...End20
.3--------------.6----------.1------10-------15------26-------45-----61------08

I know I can do a seperate equation for each of the Start, Mid, and Ends but i was hoping there was a way I could do it without having to copy/paste edit al of them individually?

2012-04-05 19:02
by jswtraveler

are you saying that the constant values could be set prior to your PROC SQL statement? or are they contained in your data set? input data would be helpfu - Jay Corbett 2012-04-05 19:12

Welcome to stackoverflow! Be sure to register. I edited your question to describe what you're using up front, and I used code formatting to clarify your code, so that it is easier for someone who hasn't see it before to understand. I hope our community can help - arboc7 2012-04-06 01:31

They could be set prior to Proc SQL or could be added to the dataset, whichever might be easier to accomplish - jswtraveler 2012-04-06 02:55

ok, provide some sample data. What would your input data set look like? What would startperiod, middleperiod, etc look like - Jay Corbett 2012-04-06 17:01

@CarolinaJay65 I put in a line of sample data above, I hope it makes sense - jswtraveler 2012-04-06 17:46

Still really unclear. What is Start1 supposed to multiply? What do you want the result for this line of input to be - itzy 2012-04-06 19:08

Could you please post the exact CREATE TABLE statement that makes your table? I think you might be able to do this by programatically creating a string that looks like an SQL UPDATE statement (using a while loop) then EXEC ing that string. I'm not usually a fan of that but it might be the best option here - Dave Collins 2012-05-01 21:34

Write a macro before you run PROC SQL to generate the repetitive code for you, then call the macro at the appropriate point within proc SQL. If you were doing this in a data step instead, an alternative option would be to define 3 arrays for the start, middle and end variables and then repeat the calculation 20 times using a do loop.

As seen here.

This source claims that there is no looping capability within PROC SQL itself - I'm not sure whether this is true or not. If it is, a macro may be the only solution.

2012-07-08 19:15
by user667489