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
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;
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.
Append all three datasets and then summarize, once with proc means. Use a CLASS statement to get subtotal and totals in one dataset.
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;
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;
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.
@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-dat... 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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.