So I have two datasets:
/* dataset A */
Col_1 Col_2 Col_3 Col_4
Row_1 3 2 5 7
Row_2 4 1 0 6
Row_3 11 8 3 1
/* dataset B */
Col_1 Col_2 Col_3 Col_4
Row_1 10 6 1 3
Row_2 2 0 8 3
Row_3 1 2 9 4
The final dataset that I need would look like:
/* dataset output */
Col_1 Col_2 Col_3 Col_4
Row_1 13 21 27 37
Row_2 6 7 15 24
Row_3 12 22 34 39
Bascially 1st sum up the same cells from dataset A and dataset B.
then calculate the cumulative sum starting from Col_1.
e.g. the Col_3, Row_2 cell value in output dataset would be calculated as: (4+2) + (0+1) + (0+8) = 15;
the Col_2, Row_1 cell: (10+3)+(2+6) = 21.
It is quite easy to do in Excel, not sure how to do it in SAS?
I would use a datastep like the following:
data A; input Col_1-Col_4; cards; 3 2 5 7 4 1 0 6 11 8 3 1 ; data B; input Col_1-Col_4; cards; 10 6 1 3 2 0 8 3 1 2 9 4 ; data want (keep=col:); set a; set b (rename=(Col_1-Col_4=_Col_1-_Col_4)); array cols(*) Col_1-Col_4; array _cols(*) _Col_1-_Col_4; do i=1 to dim(cols); if i=1 then cols(i)=sum(cols(i),_cols(i)); else do; cols(i)=sum(cols(i-1),cols(i),_cols(i)); end; end; run;
Art, CEO, AnalystFinder.com
EG Tasks -> Append two datasets into 1, then use a Summary Task to sum the records across groups.
I would use a datastep like the following:
data A; input Col_1-Col_4; cards; 3 2 5 7 4 1 0 6 11 8 3 1 ; data B; input Col_1-Col_4; cards; 10 6 1 3 2 0 8 3 1 2 9 4 ; data want (keep=col:); set a; set b (rename=(Col_1-Col_4=_Col_1-_Col_4)); array cols(*) Col_1-Col_4; array _cols(*) _Col_1-_Col_4; do i=1 to dim(cols); if i=1 then cols(i)=sum(cols(i),_cols(i)); else do; cols(i)=sum(cols(i-1),cols(i),_cols(i)); end; end; run;
Art, CEO, AnalystFinder.com
It worked fine for me.
data A;
input Col_0-Col_3;
cards;
3 2 5 7
4 1 0 6
11 8 3 1
;
data B;
input Col_0-Col_3;
cards;
10 6 1 3
2 0 8 3
1 2 9 4
;
data want (keep=col:);
set a;
set b (rename=(Col_0-Col_3=_Col_0-_Col_3));
array cols(*) Col_0-Col_3;
array _cols(*) _Col_0-_Col_3;
do i=1 to dim(cols);
if i=1 then cols(i)=sum(cols(i),_cols(i));
else do;
cols(i)=sum(cols(i-1),cols(i),_cols(i));
end;
end;
run;
My guess would be that in changing the number you also changed, but should'nt have:
do i=1 to dim(cols);
That, and the statements under it, shouldn't be changed.
Art, CEO, AnalystFinder.com
Just figured out a solution:
%macro sum;
data want (keep=Class Array:);
retain Class Array:;
array Array(*) Array_0-Array_%eval(&num.-1);
do i=1 to dim(Array);
Array(i)=0; /* give the array initial values */
end;
%do _l=1 %to &Num_type;
%let Type_name=%scan(&type,&_l);
set lib.&type;
array &Type_name.(*) &Type_name._0-&Type_name._%eval(&num.-1); /* rename each column */
do i=1 to dim(Array);
Array(i)=&Type_name.(i) + Array(i); /* sum up */
end;
%end;
run;
%mend sum;
%sum;
This process can be further expanded to cover more macro variables.
Macros are one way, but I would suggest IML or Base SAS instead.
Here's a way, doesn't matter how many datasets or variables.
data A;
input Col_1-Col_4;
cards;
3 2 5 7
4 1 0 6
11 8 3 1
;
data B;
input Col_1-Col_4;
cards;
10 6 1 3
2 0 8 3
1 2 9 4
;
data combined;
set a b indsname=source;;
*doesn't matter how many datasets here;
dsn=source;
run;
proc sort data=combined;
by dsn;
run;
data combined;
set combined;
by dsn;
if first.dsn then
row_count=1;
else row_count+1;
run;
proc means data=combined nway noprint;
class row_count;
var col_1-col_4;
*list all vars here, doesn't matter how many;
output out=want (drop = _type_ _freq_) sum=;
run;
I have no idea how you would do something like that in Excel.
But it is easy in SAS.
First combine all of the datasets and rotate into tall skinny format. Note you could make this step as a view if you want.
data combine;
set B A indsname=source ;
array c _numeric_;
if source ne lag(source) then row=0;
row+1;
do col=1 to dim(c);
value=c(col);
output;
end;
keep row col value;
run;
Then calculate the sum of the values for the same ROW*COL cell;
proc summary nway ;
class row col ;
var value ;
output out=sum (keep=row col value) sum=value ;
run;
Then generate the cummulative sums across the columns.
data cum_sum ;
do until (last.row);
set sum ;
by row col;
new_value = sum(new_value,value);
output;
end;
run;
Then if you want you could convert it back into a wide format.
proc transpose data=cum_sum out=want(drop=_: ) prefix=col_ ;
by row ;
id col ;
var new_value ;
run;
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!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.