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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.