topic Re: How to many-to-one join two tables without joined table containing duplicate values from 2nd tab in SAS Programming

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

RobertWF1 — Thu, 16 Jun 2022 16:21:06 GMT

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;

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

Kurt_Bremser — Thu, 16 Jun 2022 16:47:15 GMT

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.

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

Reeza — Thu, 16 Jun 2022 16:49:04 GMT

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?

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

RobertWF1 — Thu, 16 Jun 2022 17:26:26 GMT

Thanks Kurt! This worked

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

RobertWF1 — Thu, 16 Jun 2022 17:40:57 GMT

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.

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

Reeza — Thu, 16 Jun 2022 17:43:56 GMT

In that case I like this macro.

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

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

RobertWF1 — Mon, 20 Jun 2022 13:30:31 GMT

Thank you Reeza!