topic Re: Check source of data merging in SAS Programming

Check source of data merging

skcussas — Thu, 10 Jan 2019 21:47:29 GMT

Hi everyone,

I'm trying to merge two datasets, ds1 and ds2, and create columns to indicate the source of each row in the output dataset, dsout.

For example,

data dsout;

merge ds1 (in=a)

ds2 (in=b);

by var;

from_ds1 = a;

from_ds2 = b;

run;

I'm wondering if there's a simple way to do that by proc sql

Thanks for your help.

Re: Check source of data merging

novinosrin — Thu, 10 Jan 2019 21:58:47 GMT

Good question. Proc sql joins work by joining based on values i.e m*n product for each by group however merge does by position of by groups record by record. Therefore in= options kinda thing wouldn't be relevant from a stand point of PDV vs product cartesian(for each by group)

Of course saying that, you could create a flag to keep track I suppose

Re: Check source of data merging

kiranv_ — Thu, 10 Jan 2019 22:18:06 GMT


 data ds1;
 input var var1 $;
 datalines;
 1   x
 2   y
 ;
 
  data ds2;
 input var var2 $;
 datalines;
 2   J
 3   N
 ;
 
 
 data dsout;

    merge   ds1 (in=a)

            ds2 (in=b);

    by var;
length dataset $10.;
 if a then dataset="ds1";
 if b then dataset ="ds2";
 if a and b then dataset="ds1+ds2";
 
run;


proc sql;
create table dsout_sql1 as 
select coalesce(a.var, b.var) as var,
        var1,
        var2,
       case when a.var =b.var then "ds1+ds2"
        when a.var = . then "ds2"
        when b.var = . then "ds1" end as dataset
 from ds1 a
 full join
 ds2 b
 on a.var = b.var;

Re: Check source of data merging

PGStats — Fri, 11 Jan 2019 04:45:05 GMT

You can approximate the same operation with:

data ds1;
 input var var1 $;
 datalines;
 1   x
 2   y
 ;
 
 data ds2;
 input var var2 $;
 datalines;
 2   J
 3   N
 ;

proc sql;
select 
    *, 
    not missing(ds1.var) as inDs1,
    not missing(ds2.var) as inDs2
from
ds1 natural full join ds2
quit;

sort of...