## Nonmatching observations from 2 datasets plus additional aggregate statistics in one PROC SQL?

Occasional Contributor
Posts: 12

# Nonmatching observations from 2 datasets plus additional aggregate statistics in one PROC SQL?

Hi,

Thsi is a follow up to my previous question about non-matcing observations which had great responses! I'm trying to build on PROC SQL way to find nonmatching observations between 2 datasets and add columns with aggregate statistics from the original datasets or even some other datasets. This will help to understand better the mechanics of PROC SQL.

data dataset1;
input id \$ group \$ gender \$ age;
cards;
111 A Male 11
111 B Male 11
222 D Male 12
333 E Female 13
666 G Female 14
999 A Male 15
999 B Male 15
999 C Male 15
;
run;

data dataset2;
input id \$ group \$ gender \$ age;
cards;
111 A Male 11
999 C Male 15
;
run;
%let dsn_in1=work.dataset1;
%LET dsn_in2=work.dataset2;
%LET dsn_an=work._t_nonm3;
%LET group_var=group;
%LET var1in1=group;
%LET var1in2=group;
%LET var2in1=gender;
%LET var2in2=gender;
proc sql;        ************ 22 seconds **********;
create table &dsn_an. as
select  *,
count(distinct(&group_var.)) as group_cnt from
(select a.*,
count(a.&group_var.) as obs_per_group_cnt
from &dsn_in1. as a
left join
&dsn_in2. as b
on a.&var1in1.=b.&var1in2. and a.&var2in1.=b.&var2in2.
where a.&var1in1. ne b.&var1in2. and a.&var2in1. ne b.&var2in2.
group by a.&group_var.);
quit;

So the questions is how can I add to this &dsn_an. some group by stats from the original 2 datasets or even just some counts? Like count(distinct(a.&group_var.)) as ds1_group_cnt,
count(distinct(b.&group_var.)) as ds2_group_cnt? Can I have

count(a.&group_var.) as obs_per_group_cnt

but for for all groups/observations in dataset2 ( b)?

I have tried different ways but the best i could get is the correct count for dataset 1 and 0 for dataset even though there are clearly distinct groups in dataset 2. I aim to do this in one proc sql to improve io perf.

If there are good resources on how to manage multiple subqueries with aggregate statistics with say 2+ group by's for different variables from different datasets, that would be great!

Thank you!

Apologies for the rushed question.

Super User
Posts: 24,012

## Re: Nonmatching observations from 2 datasets plus additional aggregate statistics in one PROC SQL?

Posted in reply to astrae_research

Please show what you expect as output.

Super User
Posts: 13,941

## Re: Nonmatching observations from 2 datasets plus additional aggregate statistics in one PROC SQL?

[ Edited ]
Posted in reply to astrae_research

I'm not sure if this will help but ...

You can refer to sub query and refer to it by an alias for use as another element of a join. This is what makes SQL programming so much fun you can nest queries until it is impossible to tell what comes from where and eat lots of computer resources if you do the nesting in the wrong order.

from ( ( select id, sum(balance) from ds group by id ) as alias

left join

(some other query as long as you have the joing requirements met) as other

on id

) as combinedqueryalias

<and so forth>

The little bit I do with nested subqueries usually starts out a test query on a small subset of the data to create a table. Then the query that uses that table. At which point I can directly replace the table reference with the body of the query minus the create table bit in () with an alias. But I will make no claims as to efficiency of the result.

Posts: 5,626

## Re: Nonmatching observations from 2 datasets plus additional aggregate statistics in one PROC SQL?

Posted in reply to astrae_research

Sorry. I certainly don't understand the question. It looks as a convoluted way of doing something like this:

data dataset1;
input id \$ group \$ gender \$ age;
cards;
111 A Male 11
111 B Male 11
222 D Male 12
333 E Female 13
666 G Female 14
999 A Male 15
999 B Male 15
999 C Male 15
;

data dataset2;
input id \$ group \$ gender \$ age;
cards;
111 A Male 11
555 H Female 12
999 C Male 15
;

proc sql;
select
count(distinct group) as nbGroups,
count(distinct gender) as nbGenders
from
(select * from dataset1
union corr
select * from dataset2);
quit;
PG
Occasional Contributor
Posts: 12

## Re: Nonmatching observations from 2 datasets plus additional aggregate statistics in one PROC SQL?

Can you put this clause in the proc sql code I posted earlier? Your code seems to work but I don't know how to combine with the original proc sql code that finds nonmatching observations between 2 datasets.

Basically how do I add ( to every line) the statistics your code created to the table &dsn_an. created below in the same proc sql step?

proc sql;        ************ 22 seconds **********;
create table &dsn_an. as
select  *,
count(distinct(&group_var.)) as group_cnt from
(select a.*,
count(a.&group_var.) as obs_per_group_cnt
from &dsn_in1. as a
left join
&dsn_in2. as b
on a.&var1in1.=b.&var1in2. and a.&var2in1.=b.&var2in2.
where a.&var1in1. ne b.&var1in2. and a.&var2in1. ne b.&var2in2.
group by a.&group_var.);
quit;

The results I am looking for in one proc sql step are:

 id group gender age obs_per_group_cnt group_cnt nbgroups nbgenders 999 B Male 15 2 4 7 2 111 B Male 11 2 4 7 2 222 D Male 12 1 4 7 2 333 E Female 13 1 4 7 2 666 G Female 14 1 4 7 2

Again, apologies if this is not entirely clear.

Posts: 5,626

## Re: Nonmatching observations from 2 datasets plus additional aggregate statistics in one PROC SQL?

Posted in reply to astrae_research

What is the criteria for a match? Do you require equality on all 4 variables?

PG
Occasional Contributor
Posts: 12

## Re: Nonmatching observations from 2 datasets plus additional aggregate statistics in one PROC SQL?

Posted in reply to astrae_research

Apologies for the delay -- hardware problems.

%MACRO nonm_2var_stat(dsn_in1=,dsn_in2=,dsn_an=,group_var=,var1in1=, var1in2=, var2in1=, var2in2=,var3in1=, var3in2=);
%macro _; %mend _;
proc sql;        ************ 22 seconds **********;
create table &dsn_an. as
select  *,
count(distinct(&group_var.)) as nonm_&group_var._cnt from
(select a.*,
count(a.&group_var.) as nonm_obs_per_&group_var._cnt
from &dsn_in1. as a
full join
&dsn_in2. as b
on a.&var1in1.=b.&var1in2. and a.&var2in1.=b.&var2in2.
where a.&var1in1. ne b.&var1in2. and a.&var2in1. ne b.&var2in2.
group by a.&group_var.),
(select count(distinct c.&var1in1.) as ds1_&var1in1._cnt,
count(distinct c.&var2in1.) as ds1_&var2in1._cnt,
count(distinct(c.&group_var.)) as ds1_&group_var._cnt
from  &dsn_in1. as c),
(select count(distinct d.&var1in2.) as ds2_&var1in2._cnt,
count(distinct d.&var2in2.) as ds2_&var2in2._cnt,
count(distinct(d.&group_var.)) as ds2_&group_var._cnt
from  &dsn_in2. as d);
quit;
%MEND nonm_2var_stat;

After several tries, this code does what I wanted. Merge/join analytics for panel data. Thank you everybody!

Discussion stats
• 6 replies
• 234 views
• 0 likes
• 4 in conversation