topic Re: A left join in a proc sql instead of merge? in SAS Programming

A left join in a proc sql instead of merge?

GN0001 — Tue, 03 Aug 2021 22:49:06 GMT

Hello team,

Can we use a proc sql and left join for this code below?

Regards,

blueblue

data out.mydata;
merge thisdata(in=a) thatdata(in=b);
by hkey;
format find $15.;
if a and b then Find = 'Both';
if a and not b then Find = 'not_in_thisdata'
if a then output;
run;

Re: A left join in a proc sql instead of merge?

mkeintz — Wed, 04 Aug 2021 12:27:19 GMT

@GN0001 wrote:

Hello team,

Can we use a proc sql and left join for this code below?

Regards,

blueblue
data out.mydata;
merge thisdata(in=a) thatdata(in=b);
by hkey;
format find $15.;
if a and b then Find = 'Both';
if a and not b then Find = 'not_in_thisdata'
if a then output;
run;

If ~~(and only if)~~, for each unique HKEY value either have a one:one, many:one or a one:many match (i.e. no many:many matches), then yes you can reproduce the merge code you showed with a PROC SQL:

proc sql noprint;
  create table out.mydata as
  select *, 
     case thatdata.hkey
       when . then "Not_in_thisdata"
       else "Both"
     end
     as find
  from thisdata left join thatdata
  on thisdata.hkey=thatdata.hkey;
quit;

But why? If the data are already sorted, then the data step merge will be faster for large data sets.

Re: A left join in a proc sql instead of merge?

ChrisNZ — Wed, 04 Aug 2021 03:14:46 GMT

>If (and only if), for each unique HKEY value either have a many:one or a one:many match (i.e. no many:many matches), then yes you can reproduce the merge code you showed with a PROC SQL

one:one is also a valid case for SQL to match a data step logic

Another condition for the SQL shown to identify missing data in the right table is that the key is never missing in the data.

> why? If the data are already sorted, then the data step merge will be faster for large data sets.

I agree in principle. If the sort is validated, the difference should be minimal though as SQL will not re-sort.

Sadly SAS forgot to be clever here and does not set this flag as it should except in a few cases.

Re: A left join in a proc sql instead of merge?

ChrisNZ — Wed, 04 Aug 2021 03:32:46 GMT

All your questions about the different types of merge/join would be much faster, and much better, answered by your testing the syntaxes and examples you ask about. Doing is a much better way to learn than asking questions.

Re: A left join in a proc sql instead of merge?

GN0001 — Wed, 04 Aug 2021 06:31:04 GMT

Hi,

Do we need to bring below code in? Because left join bring all from left table and all the matches from both tables.

  case thatdata.hkey
       when . then "Not_in_thisdata"
       else "Both"
     end
     as find

Respectfully,

Blublue

Re: A left join in a proc sql instead of merge?

GN0001 — Wed, 04 Aug 2021 06:32:20 GMT

Hello,

I did test it, but I couldn't figure it out. I need an assurance.

Regards,

blueblue

Re: A left join in a proc sql instead of merge?

ChrisNZ — Wed, 04 Aug 2021 06:38:40 GMT

> Do we need to bring below code in?

The code you show will identify records present in the left table and not present in the right table, provided the data does not contain missing key values.

Re: A left join in a proc sql instead of merge?

andreas_lds — Wed, 04 Aug 2021 06:59:53 GMT

@GN0001 wrote:

Hello team,

Can we use a proc sql and left join for this code below?

Regards,

blueblue
data out.mydata;
merge thisdata(in=a) thatdata(in=b);
by hkey;
format find $15.;
if a and b then Find = 'Both';
if a and not b then Find = 'not_in_thisdata'
if a then output;
run;

So, you have a working data step, tested and giving the expected results. Why do you want to waste your time replacing it at all?

Re: A left join in a proc sql instead of merge?

Kurt_Bremser — Wed, 04 Aug 2021 07:34:02 GMT

@GN0001 wrote:

Hello,

I did test it, but I couldn't figure it out. I need an assurance.

Regards,

blueblue

<SNARK>

If you tested it, and it worked, and you don't even trust your own test, then you need to pay a visit to a shrink.

</SNARK>

There is no better way in programming to verify a code than to test it. Trusting another person that took a casual glance is foolish at best.

If you expect a trustworthy answer, we would need to test your whole code against your whole data in your environment. And then we'd start charging you.

Re: A left join in a proc sql instead of merge?

GN0001 — Sun, 08 Aug 2021 18:49:42 GMT

Hello team,

I tried and I could view the result of both.

I am clear on this one.

Thanks,

blueblue

Re: A left join in a proc sql instead of merge?

andreas_lds — Mon, 09 Aug 2021 05:52:43 GMT

Please mark the most helpful answer as solution.