Summing up all variables in a dataset..

Reply
Frequent Contributor
Posts: 76

Summing up all variables in a dataset..

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

NameRolePlaceVar 1Var 2Var 3Var 4Var 5Var 6Var 7Var 8Var 9Var 10
AAAA1A1P2404181214041862201916641797283621502625
BBBA2A2P1851263931102483138710851411254419852035
CCCA2A2P435271617202714438633167322738

Rgds, Anil

Frequent Contributor
Posts: 77

Re: Summing up all variables in a dataset..

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;

Frequent Contributor
Posts: 76

Re: Summing up all variables in a dataset..

Posted in reply to TarunKumar

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...

Respected Advisor
Posts: 3,799

Re: Summing up all variables in a dataset..

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.

Frequent Contributor
Posts: 77

Re: Summing up all variables in a dataset..

I think this will help you

Attachment
Frequent Contributor
Posts: 77

Re: Summing up all variables in a dataset..

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;

Respected Advisor
Posts: 3,799

Re: Summing up all variables in a dataset..

Posted in reply to TarunKumar

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;
Frequent Contributor
Posts: 106

Re: Summing up all variables in a dataset..

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;

Ask a Question
Discussion stats
  • 7 replies
  • 240 views
  • 0 likes
  • 4 in conversation