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

[sahoositaram555]

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.

[PaigeMiller]

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?

[sahoositaram555]

@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.

[PaigeMiller]

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.

[sahoositaram555]

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!

[PaigeMiller]

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

[Tom]

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.

[Ksharp · Accepted solution]

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;