Multiplying multiple columns in SQL [duplicate]

Question

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?

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 helpful — Jay Corbett
– Jay Corbett, Commented Apr 5, 2012 at 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
– arboc7, Commented Apr 6, 2012 at 1:31
They could be set prior to Proc SQL or could be added to the dataset, whichever might be easier to accomplish. — jswtraveler
– jswtraveler, Commented Apr 6, 2012 at 2:55
ok, provide some sample data. What would your input data set look like? What would start_period, middle_period, etc look like? — Jay Corbett
– Jay Corbett, Commented Apr 6, 2012 at 17:01
@CarolinaJay65 I put in a line of sample data above, I hope it makes sense. — jswtraveler
– jswtraveler, Commented Apr 6, 2012 at 17:46

Community · Accepted Answer · 2017-05-23 10:34:29Z

0

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.

edited May 23, 2017 at 10:34

CommunityBot

11 silver badge

answered Jul 8, 2012 at 19:15

user667489

9,5872 gold badges28 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Multiplying multiple columns in SQL [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related