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.
Please show what you expect as output.
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.
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;
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.
What is the criteria for a match? Do you require equality on all 4 variables?
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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.