Hello everyone,
I need help to calculate the aggregated (sum) total of each column in my dataset. This data set has over 100 variables. The data set is attached as an excel file.
I have tried to use proc sql and a macro do loop but I keep getting errors when processing.
PROC IMPORT OUT= _1 DATAFILE= "C:\table.xls" ;
RUN;
%macro waaa(start, end);
PROC SQL noprint;
%do j=&start %to &end ;
create table _N1 as
SELECT SUM(f&j) as _t&j
FROM _1; %end;
QUIT;
%mend;
%waaa(start=1901, end=2016);
Can anyone help?
Thanks
Proc summary data=have;
var f1901-f2016;
output out=want(drop=_type_ _freq_) sum=;
run;
Is this what you are looking for ?
Use a procedure which is designed to do summing = proc means, summary. Am afraid Excel is not an acceptable format for providing data, but I assume that the variables are all numeric (otherwise how will you sum them). Therefore using the proc means (or summary you can cheat a bit):
proc means data=have; var _numeric_; output out=want sum= /autoname; run;
Will create want with all numeric variables summed up, named <variable>_sum.
Post test data in the form of a datastep in a code window (its the {i} above post area) in future please.
Proc summary data=have;
var f1901-f2016;
output out=want(drop=_type_ _freq_) sum=;
run;
Is this what you are looking for ?
r_behata that worked!! Thank you so much. I was googling for 2 hours and couldn't find an answer. Your solution is so simple its genius!
May the stars shine on you today!
Yes, this is not appropriate use of macros here, use a PROC instead. Your code doesn't work because the SQL is embedded in the loop but has the same data set name. This means that each SQL call will overwrite the previous data. If you did want to do this in a macro, you'd have to move the loop to inside the SQL to generate the code for each variable. However, there are much easier ways.
See examples here:
https://github.com/statgeek/SAS-Tutorials/blob/master/proc_means_basic.sas
And a video tutorial here:
@yo1 wrote:
Hello everyone,
I need help to calculate the aggregated (sum) total of each column in my dataset. This data set has over 100 variables. The data set is attached as an excel file.I have tried to use proc sql and a macro do loop but I keep getting errors when processing.
PROC IMPORT OUT= _1 DATAFILE= "C:\table.xls" ;
RUN;
%macro waaa(start, end);
PROC SQL noprint;%do j=&start %to &end ;
create table _N1 as
SELECT SUM(f&j) as _t&j
FROM _1; %end;
QUIT;
%mend;
%waaa(start=1901, end=2016);
Can anyone help?
Thanks
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.