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 |
Hi Babloo,
can you please share sample data sets ? that will be really helpful to convert your code in SQL joins.
Thanks..
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).
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 |
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.
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
;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.