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

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

astrae_research — Fri, 25 Aug 2017 22:30:54 GMT

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.

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

Reeza — Fri, 25 Aug 2017 22:35:07 GMT

Please show what you expect as output.

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

ballardw — Mon, 28 Aug 2017 15:52:49 GMT

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

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.

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

PGStats — Sat, 26 Aug 2017 04:29:28 GMT

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;

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

astrae_research — Mon, 28 Aug 2017 04:19:07 GMT

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.

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

PGStats — Mon, 28 Aug 2017 04:40:02 GMT

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

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

astrae_research — Sat, 09 Sep 2017 00:22:15 GMT

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!