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 | A1 | A1P | 2404 | 1812 | 1404 | 1862 | 2019 | 1664 | 1797 | 2836 | 2150 | 2625 |
BBB | A2 | A2P | 1851 | 2639 | 3110 | 2483 | 1387 | 1085 | 1411 | 2544 | 1985 | 2035 |
CCC | A2 | A2P | 435 | 271 | 617 | 202 | 714 | 438 | 633 | 167 | 322 | 738 |
Rgds, Anil
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;
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...
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.
I think this will help you
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;
you should check out the documentation for NWAY option.
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;
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.