Pyrite | Level 9

## how to do sum of the rows and place the results in below blank row by using datasetp

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
Super User

## Re: how to do sum of the rows and place the results in below blank row by using datasetp

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
Diamond | Level 26

## Re: how to do sum of the rows and place the results in below blank row by using datasetp

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
Pyrite | Level 9

## Re: how to do sum of the rows and place the results in below blank row by using datasetp

@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.
Diamond | Level 26

## Re: how to do sum of the rows and place the results in below blank row by using datasetp

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
Pyrite | Level 9

## Re: how to do sum of the rows and place the results in below blank row by using datasetp

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!
Diamond | Level 26

## Re: how to do sum of the rows and place the results in below blank row by using datasetp

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

## Re: how to do sum of the rows and place the results in below blank row by using datasetp

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.

Super User

## Re: how to do sum of the rows and place the results in below blank row by using datasetp

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;``````
Discussion stats
• 7 replies
• 600 views
• 3 likes
• 4 in conversation