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
- /
- Base SAS Programming
- /
- How to sum vertically with missing values

Topic Options

- Subscribe to 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
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

SAS gurus,

I need to sum a column vertically which could have missing values. sometimes one dataset (dataset 3) is empty as there are no observations. once I have that done I need to sum those weights (verical sums) horizontally.

I am not sure which procedures to use to accomplish this task. please advise.

eg: __dataset 1__

variable weight

jack 12

samuel .

tylor 14

monroe 20

kelly .

total weight=46

__dataset 2__

variable weight1

kim 58

karla .

cara 25

maya 50

total weight1 =133

__dataset3__

variable weight2

.

.

.

total weight2=0

**the out put needs to have **

**total weight**

**total weight1**

**total weight2. **

**grand total= total weight+total weight1+ total weight2= 46+133+0=179**

Accepted Solutions

Solution

2 weeks ago

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

3 weeks ago

Do you know how to get a grand total for just one data set? As you might expect, SAS provides a variety of tools for that. You should learn at least one method if you expect to program with SAS, instead of relying on the board to program basic functionality for you.

SInce you have three data sets, here's a straightforward approach:

data want;

do until (done1);

set dataset1 end=done1;

total_weight + weight;

end;

do until (done2);

set dataset2 end=done2;

total_weight1 + weight1;

end;

do until (done3);

set dataset3 end=done3;

total_weight2 + weight2;

end;

grand_total = total_weight + total_weight1 + total_weight2;

keep total_: grand_total;

run;

All Replies

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

3 weeks ago

Use PROC MEANS 3 times with the SUM option and an output out= statement. Then merge the three datasets together and use the SUM function to get a grand total.

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

3 weeks ago

Append all three datasets and then summarize, once with proc means. Use a CLASS statement to get subtotal and totals in one dataset.

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

3 weeks ago

Please provide your source data always via a working SAS data step so we don't have to do this for you.

As for your question: You could first combine all your source data sets into one and and an additional variable which holds the name of the source data set. You then can use this additional variable as a classification/grouping variable in any of the SAS Procs which can return the result you're after.

I'm using Proc Tabulate in below sample code but you could also use other Procs like Proc Means, Proc Report or Proc SQL.

```
data ds1;
input name $ weight;
datalines;
jack 12
samuel .
tylor 14
monroe 20
kelly .
;
run;
data ds2;
input name $ weight;
datalines;
kim 58
karla .
;
run;
data ds3;
input name $ weight;
datalines;
cara 25
maya 50
;
run;
data ds_all;
length _inds $41;
set ds1 ds2 ds3 indsname=_inds;
inds=_inds;
run;
proc tabulate data=ds_all;
class inds;
var weight;
table inds=' ' all='Total' , weight*sum=' ';
run;
```

Solution

2 weeks ago

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

3 weeks ago

Do you know how to get a grand total for just one data set? As you might expect, SAS provides a variety of tools for that. You should learn at least one method if you expect to program with SAS, instead of relying on the board to program basic functionality for you.

SInce you have three data sets, here's a straightforward approach:

data want;

do until (done1);

set dataset1 end=done1;

total_weight + weight;

end;

do until (done2);

set dataset2 end=done2;

total_weight1 + weight1;

end;

do until (done3);

set dataset3 end=done3;

total_weight2 + weight2;

end;

grand_total = total_weight + total_weight1 + total_weight2;

keep total_: grand_total;

run;

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

2 weeks ago

Thank you all for your help. It works. Unfortunately, I have big datasets, so that is the reason I couldn't send original file. But the same logic works. Thanks all for helping me.

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

2 weeks ago

buddha_d wrote:

We don't want "big datasets" we want enough to exercise the logic. What is extremely helpful is providing data in the form of a data step.

With a data step we know the variable types and lengths. We have spend lots of time working on requests and **after** 10 or 15 responses the original poster finally admits that a value that looked numeric was actually character, or that looked character was the result of a format.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.