Help using Base SAS procedures

Proc SQL

Reply
N/A
Posts: 0

Proc SQL

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.
Super Contributor
Posts: 281

Re: Proc SQL

Posted in reply to deleted_user
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
Regular Contributor
Posts: 241

Re: Proc SQL

Posted in reply to deleted_user
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]
Ask a Question
Discussion stats
  • 2 replies
  • 117 views
  • 0 likes
  • 3 in conversation