topic How to merge two files but not concatenate ? in SAS Programming

How to merge two files but not concatenate ?

Phil_NZ — Sun, 14 Mar 2021 08:42:19 GMT

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.

Re: How to merge two files but not concatenate ?

singhsahab — Sun, 14 Mar 2021 09:31:51 GMT

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;

Re: How to merge two files but not concatenate ?

Phil_NZ — Sun, 14 Mar 2021 09:52:54 GMT

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.

Re: How to merge two files but not concatenate ?

yabwon — Sun, 14 Mar 2021 10:21:20 GMT

"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

Re: How to merge two files but not concatenate ?

Ksharp — Sun, 14 Mar 2021 11:30:53 GMT

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;

Re: How to merge two files but not concatenate ?

ballardw — Sun, 14 Mar 2021 11:37:21 GMT

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;

Re: How to merge two files but not concatenate ?

yabwon — Thu, 18 Mar 2021 08:38:14 GMT

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