Turn on suggestions

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

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- how to do sum of the rows and place the results in below blank row by ...

Options

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

☑ This topic is **solved**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 3 weeks ago
(432 views)

data xyz;

input var1 var2;

monday 13

monday 12

total .

tuesday 10

tuesday 23

total .

wednesday 34

wednesday 43

total .

run;

how to get the sum of by using var1 and place the results for total rows in var2 in data step?

i.e. no proc procedure/proc sql i shall use to get the answer.

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

It seems that is an interview question.

Nonsense for real world.

```
data xyz;
input var1 :$20. var2;
cards;
monday 13
monday 12
total .
tuesday 10
tuesday 23
total .
wednesday 34
wednesday 43
total .
;
data want;
set xyz;
cum+var2;
if var1='total' then do;var2=cum;cum=.;end;
drop cum;
run;
```

7 REPLIES 7

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Typically, this is not a good idea to store these sums in a SAS data set in the same column as the raw data. Better would be to have these results in a report of some type, or put the sums in a different column. Do you have a preference?

--

Paige Miller

Paige Miller

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

@PaigeMiller, at the moment the data structure is like this and it would be great if i get help to find a solution for a sum value for the total row which makes the further downstream work much easier.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Putting a sum in the same column as the raw data will make downstream work more difficult. Unless you already have downstream code that requires the data be in this layout.

--

Paige Miller

Paige Miller

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

It's fine if the sum value is coming in a new column along with the prerequisite values, but the intention is not to disturb the rows. i.e. basically for monday,

monday 13 13

monday 12 12

tuesday . 25

something like above would work too!

monday 13 13

monday 12 12

tuesday . 25

something like above would work too!

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

```
proc sql;
create table want as select *,sum(var2) as sum_var2
from have
group by var1;
run;
```

--

Paige Miller

Paige Miller

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

PROC SUMMARY (aka MEANS) is best tool for calculating sums.

```
data xyz;
input var1 :$9. var2;
cards;
monday 13
monday 12
tuesday 10
tuesday 23
wednesday 34
wednesday 43
;
proc summary data=xyz nway;
class var1;
var var2;
output out=total sum=;
run;
data want;
set xyz total;
by var1;
run;
```

Note that I purposely left the _TYPE_ and _FREQ_ variables that PROC SUMMARY added in the dataset so you can tell which observations are the totals.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

It seems that is an interview question.

Nonsense for real world.

```
data xyz;
input var1 :$20. var2;
cards;
monday 13
monday 12
total .
tuesday 10
tuesday 23
total .
wednesday 34
wednesday 43
total .
;
data want;
set xyz;
cum+var2;
if var1='total' then do;var2=cum;cum=.;end;
drop cum;
run;
```

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. **Registration is now open through August 30th**. Visit the SAS Hackathon homepage.

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.