topic Re: How do I select everything with 2 tables that have 1 common ID using PROC SQL? in New SAS User

How do I select everything with 2 tables that have 1 common ID using PROC SQL?

cosmid — Wed, 06 Dec 2023 20:43:10 GMT

Hi,

Sorry the Subject may be confusing.

Let's say I have two tables:

Table One:

ID x

1 a

2 b

Table Two:

ID y

1 A

3 C

proc sql;
  create table want as
  select *
  from TableOne as One, TableTwo as Two
  where One.ID = Two.ID;
quit;

Desired output:
1 a A

The above code will work but will give a WARNING and use the ID from TableOne. The correct version would be changing the SELECT statement to:

select One.ID, x, y

However, the solution only works if there's 2 variables to list. What if there are hundreds? I know it can be done using a MERGE but is there a way to write it in PROC SQL?

Thanks

Re: How do I select everything with 2 tables that have 1 common ID using PROC SQL?

ballardw — Wed, 06 Dec 2023 23:39:25 GMT

Please do not confuse data step MERGE with any of the joins. It does several things quite differently than any of the joins.

If a data step merge works for you then use that.

Your given join in Proc SQL is a Cartesian Join, meaning you would get every value of id that matches so that if you had Id=1 2 times in One and 3 times in Two you would get 6 output lines. Is that what you want?

I suggest that you go back to your example data and show example with multiple observations with the same id and then desired/expected output. Provide that example data in the form of data step code pasted into a text box such as:

Data One;
   input ID x $;
datalines;
1   a
2  b
;

data two;
  input ID y $;
datalines;
1   A
3  C
;

The desired output should be shown in a similar fashion.

Re: How do I select everything with 2 tables that have 1 common ID using PROC SQL?

Patrick — Thu, 07 Dec 2023 00:28:32 GMT

@ballardw wrote:

...

Your given join in Proc SQL is a Cartesian Join, meaning you would get every value of id that matches so that if you had Id=1 2 times in One and 3 times in Two you would get 6 output lines. Is that what you want?

@ballardw I believe you missed the where clause. This is an equijoin.

Re: How do I select everything with 2 tables that have 1 common ID using PROC SQL?

Tom — Thu, 07 Dec 2023 00:39:41 GMT

You cannot create a DATASET with two variables named ID. That is why you only get one variable. Does not really have anything to do with PROC SQL or SQL syntax per se.

Do you really need a dataset? If you just want a REPORT then just drop the CREATE TABLE part of the query and it will execute. You could use ODS EXCEL to send the report to a file you could browse in a tabular fashion.

But unless you either change the data or the code It won't return anything because 'a' does NOT equal 'A'.

Re: How do I select everything with 2 tables that have 1 common ID using PROC SQL?

ballardw — Thu, 07 Dec 2023 15:34:53 GMT

@Patrick wrote:

@ballardw wrote:

...

Your given join in Proc SQL is a Cartesian Join, meaning you would get every value of id that matches so that if you had Id=1 2 times in One and 3 times in Two you would get 6 output lines. Is that what you want?

@ballardw I believe you missed the where clause. This is an equijoin.

Don't believe I did. It is only an "equjoin" because neither set had the ID duplicated.

Data One;
   input ID x $;
datalines;
1  a
1  b
2  b
;

data two;
  input ID y $;
datalines;
1   A
1  B
1  C
3  C
;

proc sql;  create table want as
  select *
  from One, Two  where One.ID = Two.ID;
quit;

Yields 6 rows of output. Which is why I asked for clarification as to what was actually intended for the output when ID values are duplicated.

Re: How do I select everything with 2 tables that have 1 common ID using PROC SQL?

Patrick — Thu, 07 Dec 2023 22:09:23 GMT

@ballardw From the SAS docu Join Operations