topic Re: "IN" indicator for join in Enterprise Guide in SAS Programming

"IN" indicator for join in Enterprise Guide

njnagel1 — Thu, 10 Sep 2020 14:58:40 GMT

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!

Re: "IN" indicator for join in Enterprise Guide

Reeza — Thu, 10 Sep 2020 15:05:46 GMT

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

Re: "IN" indicator for join in Enterprise Guide

njnagel1 — Thu, 10 Sep 2020 15:15:44 GMT

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;

Re: "IN" indicator for join in Enterprise Guide

Reeza — Thu, 10 Sep 2020 15:22:29 GMT

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&docsetVersion=9.4&locale=en

Re: "IN" indicator for join in Enterprise Guide

njnagel1 — Thu, 10 Sep 2020 15:40:57 GMT

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&docsetVersion=9.4&locale=en#n01jepaobjvwmyn1kd3bghrqo707

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!

Re: "IN" indicator for join in Enterprise Guide

ballardw — Thu, 10 Sep 2020 15:58:23 GMT

@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&docsetVersion=9.4&locale=en#n01jepaobjvwmyn1kd3bghrqo707

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.

Re: "IN" indicator for join in Enterprise Guide

Reeza — Thu, 10 Sep 2020 16:06:55 GMT

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&docsetVersion=9.4&locale=en#n01jepaobjvwmyn1kd3bghrqo707

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!

Re: "IN" indicator for join in Enterprise Guide

njnagel1 — Thu, 10 Sep 2020 16:21:51 GMT

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!!

Re: "IN" indicator for join in Enterprise Guide

Kurt_Bremser — Thu, 10 Sep 2020 16:26:58 GMT

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.

Re: "IN" indicator for join in Enterprise Guide

Reeza — Thu, 10 Sep 2020 16:51:02 GMT

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