BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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.
2 REPLIES 2
Paige
Quartz | Level 8
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
chang_y_chung_hotmail_com
Obsidian | Level 7
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]

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
  • 624 views
  • 0 likes
  • 3 in conversation