How to many-to-one join two tables without joined table containing duplicate values from 2nd table?

[RobertWF1]

For example, in the following code notice how the table data_join contains multiple copies of the z values for A001 and B002.

Is it possible to just retain the first row of values from the matched data2 table? I'm outputting joined tables to Excel and so it's easier on the eyes if there aren't so many duplicate rows.

data data1;
input id :$4. x y;
cards;
A001 10 20
A001 30 40
A001 50 60
B002 1 2
B002 3 4
B002 5 6
;
run;

data data2;
input id :$4. z;
cards;
A001 1000
B002 2000
;
run;

proc sql;
create table data_join as
select *
from data1 a
inner join
data2 b
on a.id=b.id;
quit;

[Kurt_Bremser · Accepted solution]

Use PROC REPORT for the export:

ods xlsx file="/somewhere/file.xlsx";

proc report data=join;
column id x y z;
define id / group;
define x / display;
define y / display;
define z / group;
run;

ods excel close;

Untested, posted from my tablet.

[RobertWF1]

Thanks Kurt! This worked

[Reeza]

X/Y seem arbitrary then. Usually there's some business rule that should apply.
If they're not required can you just drop them?
Or would an aggregation of X/Y make sense, sum, mean, min?

[RobertWF1]

I made up data for my question, but the X and Y columns I need to keep (they represent proc freq row count & percent data) and the Z column represents chi-squared statistics I'm attaching to the table.

[Reeza]

In that case I like this macro.

https://communities.sas.com/t5/SAS-Communities-Library/Demographic-Table-and-Subgroup-Summary-Macro-...

[RobertWF1]

Thank you Reeza!