topic Re: Proc SQL in SAS Procedures

Proc SQL

deleted_user — Mon, 13 Sep 2010 15:14:12 GMT

I'm trying to figure out how to do something in SAS that I'd regularly do in Excel, but can't work it out.

Imagine you have two Excel sheets and you want to add the corresponding cells in a third sheet. The forumula for Sheet3 cell A2 might look like this: Sheet1!A2+Sheet2!A2. How could this be performed in SAS given two data sets, each containing many variables? Using Proc SQL, I can create one new variable at a time in the select statement but can't figure out a way to perform the calculations on the range of variables and to create a corresponding range of variables containing the outcomes of the calculations in one quick statement.

Re: Proc SQL

Paige — Mon, 13 Sep 2010 15:32:48 GMT

I don't think you can do this in "one quick statement" — at least not according to my definition of the word "quick". You could probably do this in a Macro do-loop within PROC SQL.

Another possibility is to do this in a data step. In a data step, you can merge the two data sets together and then do lots of addition of corresponding columns of variables using ARRAYs. Not what I would call "quick", but it works. Message was edited by: Paige

Re: Proc SQL

chang_y_chung_hotmail_com — Wed, 15 Sep 2010 02:21:12 GMT

It depends on what kind of ranges you are talking about. In SAS, "columns" are known as variables and "rows" observations. If you copy the formula down column-wise like:
[pre]
A2: = Sheet1!A2 + Sheet2!A2
A3: = Sheet1!A3 + Sheet2!A3
A4: = Sheet1!A4 + Sheet2!A4
...
[/pre]
then it is easy to do the similar processing in SAS. You just merge the datasets by unique id then create one new variable like below:
[pre]
data sheet1;
input id v1;
cards;
1 11
2 12
3 13
;
run;
data sheet2;
input id v2;
cards;
1 21
2 22
3 23
;
run;
data sheet3;
merge sheet1 sheet2;
by id;
v3 = v1 + v2;
run;

/* check */
proc print data=sheet3;
run;
/* on lst
id v1 v2 v3
1 11 21 32
2 12 22 34
3 13 23 36
*/
[/pre]

On the other hand, if you copy the excel formula across like:
[pre]
A2: = Sheet1!A2 + Sheet2!A2
B2: = Sheet1!B2 + Sheet2!B2
C2: = Sheet1!C2 + Sheet2!C2
...
[/pre]
then this isn't as simple as before because we have multiple variables involved. However, there are many different things in SAS that make it easy to loop over variables as well, arrays being one. hth.
[pre]
data sheet4;
input id x1 x2 x3;
cards;
1 1 2 3
;
run;
data sheet5;
input id y1 y2 y3;
cards;
1 10 20 30
;
run;
data sheet6;
merge sheet4 sheet5;
by id;
array xs{1:3} x1-x3;
array ys{1:3} y1-y3;
array zs{1:3} z1-z3;
do i = 1 to 3;
zs{i} = xs{i} + ys{i};
end;
drop i;
run;

/* check */
proc print data=sheet6;
var id z1-z3;
run;
/* on lst
id z1 z2 z3
1 11 22 33
*/
[/pre]