Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- Re: How to merge two files but not concatenate ?

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 03-14-2021 04:42 AM
(665 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

```
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;
```

6 REPLIES 6

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

```
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;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

"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

"

Hands-on-Workshop: "

"

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

SAS Documentation

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

"

Hands-on-Workshop: "

"

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

SAS Documentation

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

```
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;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;

**Available on demand!**

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

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.