DATA Step, Macro, Functions and more

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

Reply
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.

Esteemed Advisor
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:

idgroupgenderageobs_per_group_cntgroup_cntnbgroupsnbgenders
999BMale152472
111BMale112472
222DMale121472
333EFemale131472
666GFemale141472

 

 Again, apologies if this is not entirely clear.

Esteemed Advisor
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!

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