topic Re: PROC SQL : oppesite of JOIN in SAS Procedures https://communities.sas.com/t5/SAS-Procedures/PROC-SQL-oppesite-of-JOIN/m-p/163099#M42341 <HTML><HEAD></HEAD><BODY><P>You could perform a union all of one and two and then use a count function, group by and having clause to filter the results.</P></BODY></HTML> Mon, 10 Nov 2014 23:57:39 GMT Scott_Mitchell 2014-11-10T23:57:39Z PROC SQL : oppesite of JOIN https://communities.sas.com/t5/SAS-Procedures/PROC-SQL-oppesite-of-JOIN/m-p/163094#M42336 <HTML><HEAD></HEAD><BODY><P>Hello,</P><P></P><P>in my University ive got this task:</P><P></P><P>ONE</P><P>red</P><P>green</P><P>brown</P><P>brown</P><P></P><P>TWO</P><P>purple</P><P>green</P><P>brown</P><P></P><P>my goal:</P><P>is get the colors who only shown once</P><P>with INNER RIGHT LEFT FULL JOIN</P><P>in this case</P><P></P><P>red</P><P>purple</P><P></P><P>i just wanna have a hint</P><P>wanna do the rest by my self <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P><P></P><P>Thanks</P></BODY></HTML> Sun, 09 Nov 2014 20:26:31 GMT https://communities.sas.com/t5/SAS-Procedures/PROC-SQL-oppesite-of-JOIN/m-p/163094#M42336 suicidedoor 2014-11-09T20:26:31Z Re: PROC SQL : oppesite of JOIN https://communities.sas.com/t5/SAS-Procedures/PROC-SQL-oppesite-of-JOIN/m-p/163095#M42337 <HTML><HEAD></HEAD><BODY><P><A href="http://www.w3schools.com/sql/sql_join_inner.asp" title="http://www.w3schools.com/sql/sql_join_inner.asp">SQL INNER JOIN Keyword</A> (just google to study/learning examples)&nbsp; SAS is supporting Ansi SQL with some minor details being different. </P></BODY></HTML> Sun, 09 Nov 2014 20:30:55 GMT https://communities.sas.com/t5/SAS-Procedures/PROC-SQL-oppesite-of-JOIN/m-p/163095#M42337 jakarman 2014-11-09T20:30:55Z Re: PROC SQL : oppesite of JOIN https://communities.sas.com/t5/SAS-Procedures/PROC-SQL-oppesite-of-JOIN/m-p/163096#M42338 <HTML><HEAD></HEAD><BODY><P>That's quite a good task as it will make you dive into the workings of SQL. One way to go: FULL JOIN with a HAVING clause - and eventually a COALESCE() in the SELECT</P><P>Another option here: <A href="http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/viewer.htm#a001361224.htm" style="font-size: 10pt; line-height: 1.5em;" title="http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/viewer.htm#a001361224.htm">SAS(R) 9.2 SQL Procedure User's Guide</A></P></BODY></HTML> Sun, 09 Nov 2014 21:26:54 GMT https://communities.sas.com/t5/SAS-Procedures/PROC-SQL-oppesite-of-JOIN/m-p/163096#M42338 Patrick 2014-11-09T21:26:54Z Re: PROC SQL : oppesite of JOIN https://communities.sas.com/t5/SAS-Procedures/PROC-SQL-oppesite-of-JOIN/m-p/163097#M42339 <HTML><HEAD></HEAD><BODY><P>The following&nbsp; image may help you viisualize the various types of SQL joins...</P><P></P><P><IMG __jive_id="7852" alt="image.jpg" class="jive-image-thumbnail jive-image" src="https://communities.sas.com/legacyfs/online/7852_image.jpg" width="450" /></P></BODY></HTML> Mon, 10 Nov 2014 06:31:37 GMT https://communities.sas.com/t5/SAS-Procedures/PROC-SQL-oppesite-of-JOIN/m-p/163097#M42339 MichelleHomes 2014-11-10T06:31:37Z Re: PROC SQL : oppesite of JOIN https://communities.sas.com/t5/SAS-Procedures/PROC-SQL-oppesite-of-JOIN/m-p/163098#M42340 <HTML><HEAD></HEAD><BODY><P>Hi (suicidedoor?) - do you specifically have to use a JOIN clause. Your desired result could be achieved in SQL using SET operators.</P></BODY></HTML> Mon, 10 Nov 2014 22:18:41 GMT https://communities.sas.com/t5/SAS-Procedures/PROC-SQL-oppesite-of-JOIN/m-p/163098#M42340 AndrewHowell 2014-11-10T22:18:41Z Re: PROC SQL : oppesite of JOIN https://communities.sas.com/t5/SAS-Procedures/PROC-SQL-oppesite-of-JOIN/m-p/163099#M42341 <HTML><HEAD></HEAD><BODY><P>You could perform a union all of one and two and then use a count function, group by and having clause to filter the results.</P></BODY></HTML> Mon, 10 Nov 2014 23:57:39 GMT https://communities.sas.com/t5/SAS-Procedures/PROC-SQL-oppesite-of-JOIN/m-p/163099#M42341 Scott_Mitchell 2014-11-10T23:57:39Z