"IN" indicator for join in Enterprise Guide

[njnagel1]

Hello,

I'm looking for how to add the "IN" indicator to a file join in Enterprise Guide's GUI. I tried to go to Join Properties and add in=inmaster in the option box, but I get a syntax error when I try to run.  It says Invalid Option Name IN. This is easy in SAS Studio! Help please. Thanks!

[Reeza]

Can you show an example of the query you're trying to generate?

[njnagel1]

Sure! In SAS Studio, it would be 
Data merged;   merge data1 (in=indata1) data2 (in=indata2);   by id;run;

EG generates PROC SQL code that errors out (below)-

PROC SQL NOEXEC; SELECT t1.TRI2, t1.TOTAL_PAID_AMT,  t1.TOTAL_BILLED_AMT,  t1.CLAIM_TYPE, t2.REC_IND,  t2.AMT_PAID,  t2.AMT_BILLED  FROM WORK.QUERY_FOR_SORTSORTED_0001(in=inmaster) t1 12 INNER JOIN WORK.QUERY_FOR_SORTSORTED_0002(in=inmrr) t2 ON (t1.TRI2 = t2.TRI2);

ERROR: Invalid option name IN. ERROR: Some options for file WORK.QUERY_FOR_SORTSORTED_0001 were not processed because of errors or warnings noted above. ERROR: Invalid option name IN. ERROR: Some options for file WORK.QUERY_FOR_SORTSORTED_0002 were not processed because of errors or warnings noted above. 13 QUIT;

[Reeza]

IN isn't valid in SQL. It doesn't work in SAS Studio either, just tested it.
It's a data step option not a SQL step. What are you trying to do with that data? There are other ways to calculate it in SQL.

Note for Valid IN = Data Step, SQL is not included. In comparison DROP is valid in data steps and Procs.
https://go.documentation.sas.com/?docsetId=ledsoptsref&docsetTarget=n1p1o2dsuc465nn198ovwdrj9mvy.htm...

[njnagel1]

There's alot on the web on the use of IN in SAS file merging. See two examples here:

https://documentation.sas.com/?docsetId=lestmtsref&docsetTarget=n1i8w2bwu1fn5kn1gpxj18xttbb0.htm&doc...

Regardless, I'm matching two datasets and want to separate the results into those records that have a match in data1 (master) from those without a match. So I join and use the indicators of indata1=1 and indata2=1 VS indata1=1 and indata2=0 VS indata1=0 and indata2=1.

Thank you for your assistance!

[ballardw · Accepted solution]

---

@njnagel1 wrote:

There's alot on the web on the use of IN in SAS file merging. See two examples here:

https://documentation.sas.com/?docsetId=lestmtsref&docsetTarget=n1i8w2bwu1fn5kn1gpxj18xttbb0.htm&doc...

Regardless, I'm matching two datasets and want to separate the results into those records that have a match in data1 (master) from those without a match. So I join and use the indicators of indata1=1 and indata2=1 VS indata1=1 and indata2=0 VS indata1=0 and indata2=1.

Thank you for your assistance!

---

The link you use shows DATA STEP code, not Proc SQL.

Using SQL will require a few more steps and types of joins to achieve similar results. One thing is that SQL will AFAIK only allow a single table creation with a single SQL call.  If you want to use SQL you would be looking at EXCEPT operation and going both ways to do the equivalent of indata1=1 and indata2=0 VS indata1=0 and indata2=1. INNER JOIN does the in both.

[Reeza]

Your code examples are DATA STEPs not PROCs. 

IN works in data steps, it does not work in PROCs including PROC SQL.

When joining data via SQL you control the merge primarily using the type of join, ie left/right/inner/outer.

Here's a short tutorial on SQL joins:

https://www.listendata.com/2014/06/proc-sql-merging.html

---

@njnagel1 wrote:

There's alot on the web on the use of IN in SAS file merging. See two examples here:

https://documentation.sas.com/?docsetId=lestmtsref&docsetTarget=n1i8w2bwu1fn5kn1gpxj18xttbb0.htm&doc...

Regardless, I'm matching two datasets and want to separate the results into those records that have a match in data1 (master) from those without a match. So I join and use the indicators of indata1=1 and indata2=1 VS indata1=1 and indata2=0 VS indata1=0 and indata2=1.

Thank you for your assistance!

---

[njnagel1]

I'm trying to accomplish this using the Enterprise Guide GUI, and it generates SQL code. I'll need to modify the code.

So I'm guessing that there is not a way to specify the IN option in Enterprise Guide, besides modifying the SQL code...

Thanks for the help!!

[Kurt_Bremser]

In EG, you either use the query builder, which creates SQL code, or write data step code in a program node. It is (as always, see Maxim 14) a question of the right tool for the issue at hand. Code you wrote in SAS Studio will also work in EG.

[Reeza]

In SQL you use the join type INSTEAD of "in", different language = different methods.