BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Phil_NZ
Barite | Level 11

Hi all SAS Users,

 

Today I faced a problem relating to the Table display. Normally we have average at the final row of a file. 

 

My dataset is

LOC            x1       x2          develop
AUS            10       20           1
AUT            15       25           1
BEL            8        21           1
ARG            3         5           0
BGR            5         6           0
BRA            1         4           0

What I want is

LOC            x1       x2          
AUS            10       20          
AUT            15       25           
BEL             8       21           
Average        11       22
ARG            3         5           
BGR            5         6          
BRA            1         4           
Average        3         5

I can calculate the average by proc means easily but I do not know how to present like that.

My code is

proc sort data=have;
by descending develop;
run;
proc means data=have noprint nway; class develop; var x1 x2; output out=want mean=meanx1 meanx2; run;

That I have output "want":

develop    meanx1   meanx2
1          11       22
0          3        5

But I do not know how to display what I want above.

 

Warmest regards.

 

Thank you for your help, have a fabulous and productive day! I am a novice today, but someday when I accumulate enough knowledge, I can help others in my capacity.
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
data have;
input LOC $  x1       x2          develop;
cards;
AUS  10       20 1
AUT  15       25 1
BEL  8        21 1
ARG  3         5 0
BGR  5         6 0
BRA  1         4 0
;
run;
proc report data=have nowd;
columns  loc x1 x2  develop;
define develop/group noprint descending;
define loc/display;
define x1/analysis mean;
define x2/analysis mean;
compute after develop;
loc='Average';
endcomp;
break after develop/summarize;
run;

View solution in original post

6 REPLIES 6
singhsahab
Lapis Lazuli | Level 10
data have;
input LOC $3.  x1       x2          develop;
cards;
AUS  10       20 1
AUT  15       25 1
BEL  8        21 1
ARG  3         5 0
BGR  5         6 0
BRA  1         4 0
;
run;

proc sql noprint;
create table have1 as
select *,avg(x1) as avg_x1,avg(x2) as avg_x2 from have group by develop;
quit;

proc sort data=have1
 out=have2(keep= avg_x1 avg_x2) nodupkey; by avg_x1 avg_x2 ;run;

 proc sql noprint;
 create table want as 
 select loc,x1,x2 from have 
 union all
 select 'Average' as loc,avg_x1 as x1,avg_x2 as x2 from have2
 quit;
Phil_NZ
Barite | Level 11

Hi @singhsahab 

Thank you for your suggestion, but your code ends up like that

LOC	      x1	x2
AUS	      10	20
AUT	      15	25
BEL	       8	21
ARG	       3	5
BGR	       5	6
BRA	       1	4
Average	   3	5
Average	   11	22

Could you please help me to sort it out?

Warm regards.

Thank you for your help, have a fabulous and productive day! I am a novice today, but someday when I accumulate enough knowledge, I can help others in my capacity.
yabwon
Onyx | Level 15

"Use the BY-group processing Luke" 🙂

 

data have;
input LOC $ 3. x1 x2 develop;
cards;
AUS            10       20           1
AUT            15       25           1
BEL            8        21           1
ARG            3         5           0
BGR            5         6           0
BRA            1         4           0
;
run;
proc print;
run;

data want;
  length LOC $ 7;
  set have;
  by descending develop;

  if first.develop then
    do;
      _sumX1 = 0;
      _countX1 = 0;
      _sumX2 = 0;
      _countX2 = 0;
    end;

  _sumX1 + X1;
  _countX1 + (X1 > .z);
  _sumX2 + X2;
  _countX2 + (X2 > .z);

  output;

  if last.develop then
    do;
      LOC = "Average";
      X1 = _sumX1 / _countX1;
      X2 = _sumX2 / _countX2;
      output;
    end;
  drop develop _:;
run;
proc print;
run;

If you have more Xes (X1, X2,..., Xn) adding the "use of arrays" may be useful.

 

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



yabwon
Onyx | Level 15

Just for fun, version with arrays:

data have;
input LOC $ 3. x1 x2 develop;
x3 = 2 * x1;
x4 = 2 * x2;
cards;
AUS 10 20 1
AUT 15 25 1
BEL  8 21 1
ARG  3  5 0
BGR  5  6 0
BRA  1  4 0
;
run;
proc print;
run;

%let NoX= 4;

data want;
  length LOC $ 12;
  set have;
  by descending develop;
  array X(_i_) x1-x&NoX.;
  array S(_i_) _sumX1-_sumX&NoX.;
  array C(_i_) _countX1-_countX&NoX.;

  if first.develop then
    do over S;
      S = 0;
      C = 0;
    end;

  do over X;
    S + X;
    C + (X > .z);
  end;
  output;

  if last.develop then
    do;
      LOC = catx(" ", "Average", develop);
      do over X;
        X = S / C;
      end;
      output;
    end;
  drop develop _:;
run;
proc print;
run;

 

B-)

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Ksharp
Super User
data have;
input LOC $  x1       x2          develop;
cards;
AUS  10       20 1
AUT  15       25 1
BEL  8        21 1
ARG  3         5 0
BGR  5         6 0
BRA  1         4 0
;
run;
proc report data=have nowd;
columns  loc x1 x2  develop;
define develop/group noprint descending;
define loc/display;
define x1/analysis mean;
define x2/analysis mean;
compute after develop;
loc='Average';
endcomp;
break after develop/summarize;
run;
ballardw
Super User

I'm not fond of putting summary values into data as one misremember it is there and a model blows up for validity.

 

data have;
input LOC $3.  x1       x2          develop;
cards;
AUS  10       20 1
AUT  15       25 1
BEL  8        21 1
ARG  3         5 0
BGR  5         6 0
BRA  1         4 0
;
run;

proc report data=have;
   columns develop loc x1 x2;
   define develop / group noprint order=data;
   define loc /group order=data;
   define x1 /mean;
   define x2 /mean;
   break after develop/summarize;
run;

SAS Innovate 2025: Call for Content

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!

Submit your idea!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 928 views
  • 12 likes
  • 5 in conversation