topic Re: Need help calculating(summing) several variables as columns and not rows in SAS Programming

Need help calculating(summing) several variables as columns and not rows

yo1 — Tue, 20 Nov 2018 16:21:02 GMT

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

Re: Need help calculating(summing) several variables as columns and not rows

RW9 — Tue, 20 Nov 2018 16:26:20 GMT

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.

Re: Need help calculating(summing) several variables as columns and not rows

r_behata — Tue, 20 Nov 2018 16:29:51 GMT

Proc summary data=have;
var f1901-f2016;
output out=want(drop=_type_ _freq_) sum=;
run;

Is this what you are looking for ?

Re: Need help calculating(summing) several variables as columns and not rows

Reeza — Tue, 20 Nov 2018 16:31:03 GMT

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:

https://video.sas.com/detail/video/3306906230001/summary-statistics-using-sas-studio?autoStart=true&q=summary%20statistics

@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

Re: Need help calculating(summing) several variables as columns and not rows

yo1 — Tue, 20 Nov 2018 16:36:12 GMT

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!