topic Re: How to sum vertically with missing values in SAS Programming

How to sum vertically with missing values

buddha_d — Tue, 01 Aug 2017 05:02:17 GMT

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

Re: How to sum vertically with missing values

PeterClemmensen — Tue, 01 Aug 2017 05:11:17 GMT

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.

Re: How to sum vertically with missing values

Reeza — Tue, 01 Aug 2017 05:40:05 GMT

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

Re: How to sum vertically with missing values

Patrick — Tue, 01 Aug 2017 05:46:19 GMT

@buddha_d

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;

Re: How to sum vertically with missing values

Astounding — Tue, 01 Aug 2017 06:04:27 GMT

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;

Re: How to sum vertically with missing values

buddha_d — Tue, 01 Aug 2017 11:27:13 GMT

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.

Re: How to sum vertically with missing values

ballardw — Tue, 01 Aug 2017 22:41:09 GMT

@buddha_d wrote:

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.

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-data-AKA-generate/ta-p/258712 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.