topic Re: Summing up all variables in a dataset.. in SAS Programming

Summing up all variables in a dataset..

aj34321 — Tue, 29 Oct 2013 08:56:55 GMT

D/All,

I'm looking for code which will sum up all variables in dataset. The variables in the dataset is unknown, it can be any number and names also dynamically generated. Below is the sample data.

Request if someone can help me on this.

Name	Role	Place	Var 1	Var 2	Var 3	Var 4	Var 5	Var 6	Var 7	Var 8	Var 9	Var 10
AAA	A1	A1P	771	532	152	329	319	101	690	484	909	832
BBB	A2	A2P	293	944	352	231	138	291	527	898	805	171
BBB	A2	A2P	401	691	964	751	580	164	297	135	364	465
BBB	A2	A2P	668	293	931	818	383	526	320	675	578	762
AAA	A1	A1P	778	825	255	878	930	782	184	975	676	456
AAA	A1	A1P	569	142	619	126	250	374	107	583	204	431
CCC	A3	A3P	435	271	617	202	714	438	633	167	322	738
AAA	A1	A1P	286	313	378	529	520	407	816	794	361	906
BBB	A2	A2P	489	711	863	683	286	104	267	836	238	637

Name

Role

Place

Var 1

Var 2

Var 3

Var 4

Var 5

Var 6

Var 7

Var 8

Var 9

Var 10

AAA

A1P

2404

1812

1404

1862

2019

1664

1797

2836

2150

2625

BBB

A2P

1851

2639

3110

2483

1387

1085

1411

2544

1985

2035

CCC

A2P

435

271

617

202

714

438

633

167

322

738

Rgds, Anil

Re: Summing up all variables in a dataset..

TarunKumar — Tue, 29 Oct 2013 09:28:07 GMT

Use proc sql sample code is there and you wil got the required oupt.

Select name,role,place,sum(Var1) As Var1,

sum(Var2) As Var2,

sum(Var3) As Var3,

sum(Var4) As Var4,

sum(Var5) As Var5,

sum(Var6) As Var6,

sum(Var7) As Var7,

sum(Var8) As Var8,

sum(Var9) As Var9,

sum(Var10) As Var10,

from, My_data

group by name,role,place;

quit;

Re: Summing up all variables in a dataset..

aj34321 — Tue, 29 Oct 2013 09:58:50 GMT

Thanks Tarun,

This is possible when i have limited number for columns and when they are definite columns. My variables are getting generated dynamically and i dont know the names of variables.

I was looking forward using proc means..

Thanks anyways...

Re: Summing up all variables in a dataset..

data_null__ — Tue, 29 Oct 2013 10:22:50 GMT

You should look at the documentation for PROC MEANS. The default action is to analyze all numeric variables not use in other statements CLASS BY etc. You can use ODS to create a dataset of the statistics you need.

Re: Summing up all variables in a dataset..

TarunKumar — Tue, 29 Oct 2013 11:07:45 GMT

I think this will help you

Re: Summing up all variables in a dataset..

TarunKumar — Tue, 29 Oct 2013 11:17:08 GMT

data my_data;
input Name:$3. Role:$2. Place:$3. Var1 Var2 Var3 Var4 Var5 Var6 Var7 Var8 Var9 Var10 ;
datalines;
AAA A1 A1P 771 532 152 329 319 101 690 484 909 832
BBB A2 A2P 293 944 352 231 138 291 527 898 805 171
BBB A2 A2P 401 691 964 751 580 164 297 135 364 465
BBB A2 A2P 668 293 931 818 383 526 320 675 578 762
AAA A1 A1P 778 825 255 878 930 782 184 975 676 456
AAA A1 A1P 569 142 619 126 250 374 107 583 204 431
CCC A3 A3P 435 271 617 202 714 438 633 167 322 738
AAA A1 A1P 286 313 378 529 520 407 816 794 361 906
BBB A2 A2P 489 711 863 683 286 104 267 836 238 637
;
run;

data my_data_1;
set my_data;
new_class = compress(name||role||place);
drop name role place;
run;

proc means data=my_data_1 ;class new_class ;output out= my_data_2 sum=;run;
data my_data_2_1;
set my_data_2;
if new_class = ' ' then delete;
run;

Re: Summing up all variables in a dataset..

pradeepalankar — Tue, 29 Oct 2013 11:23:11 GMT

you can try this one:

proc sql feedback;

select compress(name||")")||" as "||name into :var_name separated by " , sum( "

from dictionary.columns where LIBNAME="WORK" and memname="MY_DATA" and upcase(type)="NUM";

create table output as

select name,role,place,sum(&var_name from MY_DATA

group by name,role,place;

quit;

Re: Summing up all variables in a dataset..

data_null__ — Tue, 29 Oct 2013 11:56:22 GMT

you should check out the documentation for NWAY option.

proc means data=my_data noprint nway; 
   class Name Role Place;
   output out=sum sum=;
   run;