turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Proc SQL

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-13-2010 11:14 AM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

09-13-2010 11:32 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

09-14-2010 10:21 PM

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]

[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]