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

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

sahoositaram555 — Wed, 24 Jul 2024 12:52:08 GMT

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.

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

PaigeMiller — Wed, 24 Jul 2024 13:02:48 GMT

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?

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

sahoositaram555 — Wed, 24 Jul 2024 13:05:48 GMT

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

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

PaigeMiller — Wed, 24 Jul 2024 13:07:27 GMT

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.

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

sahoositaram555 — Wed, 24 Jul 2024 13:13:24 GMT

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!

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

PaigeMiller — Wed, 24 Jul 2024 13:18:40 GMT

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

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

Tom — Wed, 24 Jul 2024 15:53:28 GMT

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.

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

Ksharp — Thu, 25 Jul 2024 00:39:47 GMT

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;