topic Re: Merge Vs Join in SAS Programming

Merge Vs Join

Babloo — Tue, 30 Oct 2018 11:09:21 GMT

How to convert the following merge step into joins. I just given the structure of the merge and in similar fashion I need to achieve the same in Joins. 'Khiref' is the variable which is common between the tables and it the relationship is many-to-many.

data want (drop=knkvpb knkvpb_v);
	merge work.varian1(in=vorige) 
		in.grptabel(in=huidige);
	by khiref;

	if not vorige 
		and huidige then
		do;
			knkbty = '6';
			knkatv = knkatd_v;
			knkath = knkatd;
		end;

	if vorige 
		and not huidige then
		do;
			knkbty='6';
			knkatv = knkatd_v;
			knkath = knkatd;
		end;

	if vorige 
		and huidige then
		do;
			if (knkatd ne knkatd_v) then
				do;
					knkbty = '6';
					knkatv = knkatd_v;
					knkath = knkatd;
				end;
run;

Sample data:

Below data is being used as input data sets but values of few variables differ between the datasets..

KHIREF	KNDTOPD	TLK	KNKRSG	INKWZK	KNKKRT	KNKATD	KNDATLD	BVILTS	BVDBLTD	PODVERD	KNDBEGD	KNDEIND	KNKBIN	POKSYN	KNKTAS	KNKBZA	MIK	ZTK	PSI	AGI	PUKDET	KNBJPN	KNBCOW	KNBTKS	KNBKST	AGIABR	POKTYD	PDK	KNKVPB	GNKSEC	D1IPAR	P2KBRN	KNBJPB	KNBKRT	P2KPSH	KNBCOT	KNBCOA	POKBDU
2011021557	30/09/2018	1	0	1	3	1	01/07/2008	1	11/05/2013	01/07/2019	01/07/2008	30/06/2019	0	0	0	1	3	10	418210038	45025	999	262.38	39.36	71.08	0.00	0	2	1	0	92094F00-	28	16	262.38	0.00	1	79.72	40.36	0
2011021549	30/09/2018	1	0	1	3	1	01/07/2008	1	11/05/2013	01/07/2019	01/07/2008	30/06/2019	0	0	0	1	3	10	418210038	31397	999	524.76	78.72	142.16	0.00	0	2	1	0	92094F00-	28	16	524.76	0.00	1	79.72	80.72	0

Re: Merge Vs Join

singhsahab — Tue, 30 Oct 2018 10:10:35 GMT

Hi Babloo,

can you please share sample data sets ? that will be really helpful to convert your code in SQL joins.

Thanks..

Re: Merge Vs Join

Kurt_Bremser — Tue, 30 Oct 2018 10:28:54 GMT

Your data _null_ step creates nothing, so the "conversion" to SQL means to simply do nothing.

If that was just a mistake, supply example data, and describe the relationship of the tables with regard to khiref (ono-to-one, one-to-many, many-to-many).

Re: Merge Vs Join

Babloo — Tue, 30 Oct 2018 11:10:20 GMT

I corrected the OP now. Sample data is same for both the datasets except the values of few variables including KHIREF.

KHIREF	KNDTOPD	TLK	KNKRSG	INKWZK	KNKKRT	KNKATD	KNDATLD	BVILTS	BVDBLTD	PODVERD	KNDBEGD	KNDEIND	KNKBIN	POKSYN	KNKTAS	KNKBZA	MIK	ZTK	PSI	AGI	PUKDET	KNBJPN	KNBCOW	KNBTKS	KNBKST	AGIABR	POKTYD	PDK	KNKVPB	GNKSEC	D1IPAR	P2KBRN	KNBJPB	KNBKRT	P2KPSH	KNBCOT	KNBCOA	POKBDU
2011021557	30/09/2018	1	0	1	3	1	01/07/2008	1	11/05/2013	01/07/2019	01/07/2008	30/06/2019	0	0	0	1	3	10	418210038	45025	999	262.38	39.36	71.08	0.00	0	2	1	0	92094F00-	28	16	262.38	0.00	1	79.72	40.36	0
2011021549	30/09/2018	1	0	1	3	1	01/07/2008	1	11/05/2013	01/07/2019	01/07/2008	30/06/2019	0	0	0	1	3	10	418210038	31397	999	524.76	78.72	142.16	0.00	0	2	1	0	92094F00-	28	16	524.76	0.00	1	79.72	80.72	0

Re: Merge Vs Join

Kurt_Bremser — Tue, 30 Oct 2018 11:51:38 GMT

Using a data step to merge datasets that contain the same variables will not give you the expected results reliably.

Post usable (data step with datalines!) examples for both datasets, and what you want to get out of it.

Re: Merge Vs Join

s_lassen — Tue, 30 Oct 2018 12:39:19 GMT

Not sure if this is what you want, but it sounds like you want to use SQL instead of merge in order to get a cartesian product, so here is a suggestion:

proc sql;
  create table want as select
    coalesce(vorige.khiref,huidige.khiref) as khiref,
    case
       when vorige.khiref is null or huidige.khiref is null then '6' 
       when knkatd ne knkatd_v then '6' 
       else huidige.knbkty /* or is it vorige.knbkty? */
    end as knkbty,
    /* rest of columns */
    from Work.varian1 as vorige
     full join in.grptabel as huidige
      on vorige.khiref=huidige.khiref
    ;

Re: Merge Vs Join

Shmuel — Tue, 30 Oct 2018 12:44:26 GMT

In SQL you'll need three joins:

1) for "if vorige and huidige then" - you need FULL JOIN.

2)for "if not vorige and huidige then"- you need RIGHT JOIN.

3) for "if vorige and not huidige then"- you need LEFT JOIN.

skeleton of the sql will be like:

proc sql;
       create table want as 
       select *,
                  '6' as knkbty,
                  knkatv as knkatd_v,
                  knkath as knkatd
        from wok.varian1 as a  righ join in.grptabel as b
          on a.khiref = b.khiref ;

       /* add two more SELECT satements similar to above */

      order by khiref;
quit;

BTW - I see no difference dealing the three situations - in all three your code is the same:

do;
	knkbty='6';
	knkatv = knkatd_v;
	knkath = knkatd;
end;