BookmarkSubscribeRSS Feed
astrae_research
Obsidian | Level 7

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.

 

6 REPLIES 6
ballardw
Super User

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.

PGStats
Opal | Level 21

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
astrae_research
Obsidian | Level 7

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.

PGStats
Opal | Level 21

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

PG
astrae_research
Obsidian | Level 7

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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