topic How to achieve SAS merge in SQL joins in SAS Programming

How to achieve SAS merge in SQL joins

yadavdharmendra — Mon, 13 Dec 2021 17:04:51 GMT

I am converting SAS code to Spark. In one of the data step SAS is doing merge (many to many). I need to achieve same in Spark. Please help me to get this done.

proc sort data=dat1;by mbr_sys_id clm_aud_nbr2;run;
proc sort data=dat2;by mbr_sys_id clm_aud_nbr2;run;

data want;
 merge dat1(in=a)
    dat2(in=b);
    by mbr_sys_id clm_aud_nbr2;
    if a and b;
run;

I want to get this same output using proc sql. Can you please help me in here.

Re: How to achieve SAS merge in SQL joins

maguiremq — Mon, 13 Dec 2021 17:47:48 GMT

DATA step merges cannot do many-to-many joins.

Note: The MERGE statement does not produce a Cartesian product on a many-to-many match-merge. Instead, it performs a one-to-one merge while there are observations in the BY group in at least one data set. When all observations in the BY group have been read from one data set and there are still more observations in another data set, SAS performs a one-to-many merge until all BY group observations have been read.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lestmtsref/n1i8w2bwu1fn5kn1gpxj18xttbb0.htm

What you're showing is an inner join.

You also need to be careful if you share any variable names in the data sets in a MERGE statement. SAS takes the right-most value.

Re: How to achieve SAS merge in SQL joins

SASKiwi — Mon, 13 Dec 2021 19:01:48 GMT

The following SQL will do a many-to-many join which is similar but not the same as a SAS MERGE:

proc sql;
  create table want as 
  select *
  from dat1 as A
  inner join dat2 as B
  on A.mbr_sys_id = B.mbr_sys_id
  and A.clm_aud_nbr2 = B.clm_aud_nbr2
  ;
quit;

Re: How to achieve SAS merge in SQL joins

yadavdharmendra — Tue, 14 Dec 2021 11:39:49 GMT

I can do this inner join but how can I achieve same out which sas merge produces .

Actually bigger picture is I am converting SAS code to SPARK 3 I do not have merge in spark

only i have joins, if you can replicate SAS merge output into sql , it will be helpful for me.

Re: How to achieve SAS merge in SQL joins

LinusH — Tue, 14 Dec 2021 12:01:21 GMT

As already stated, SQL join and data step merge are not the same.

Under many circumstances they produce the same result.

But for you to be able to migrate in a consistent way, you need to understand the requirement behind the SAS code, and how your input data behaves,