0

I want to union the same table this way

ID B C D E
1 11 12 13 14
2 21 22 23 24
3 31 32 33 34

to 

ID B C D E
1 11 12 13 14
2 21 22 23 24
3 31 32 33 34
11 13 14 
12
21 23 24
22
31 33 34
32

based on the IDs of a third table. (I want to have only those IDs which are present in a third table)

third table 

ID
1
3
21
31

I am able to do the union 

proc sql ; 
create table bb as 
select * from first 
union
select B, D, E from first
union
select C from first

;
quit;

here is where I am stuck. how to select only those IDs present in a third table.

Joe
  • 62,789
  • 6
  • 49
  • 67
user3658367
  • 641
  • 1
  • 14
  • 30
  • Are you saying you want to pull from the first table three different times? Do you want the value of C from the third select to go into the same column as the C from the first select? Or do you want them to be treated like the ID column in the first select? – Tom Apr 08 '16 at 17:02
  • Ok. Let me explain. I want to create the second table from the first table. and then I want to have a clause here to take only those IDs which are there in the third table. is it possible? – user3658367 Apr 08 '16 at 17:09
  • I will ignore how to make the second table since the requirements aren't clear and answer how to subset to those that are in the third table. – Tom Apr 08 '16 at 17:17

2 Answers2

1

Can't you just use a sub-query?

proc sql ; 
  create table want as 
     select B.*
     from
        (select * from first 
         union
         select B, D, E from first
         union
         select C from first
        ) B
     inner join THIRD c on B.id = C.id
  ;
quit;

Plug whatever logic makes the second table inside the ().

Tom
  • 47,574
  • 2
  • 16
  • 29
  • this is very useful., and one more question can we have a select statement after inner join if I want a particular column from the third? or if I dont want any columns from the third (how do i do it) – user3658367 Apr 08 '16 at 17:35
  • Not sure I understand the question. The list of variables is at the beginning of the SELECT statement. If you want the variable X from the dataset THIRD then add `,c.X` (since I use C as the alias for THIRD) to the list of variable in the SELECT statement. – Tom Apr 09 '16 at 02:29
0

You want to union FIRST and SECOND and then join to THIRD, or alternately join THIRD to both FIRST and SECOND; it's unclear if the SQL optimizer will do the latter for you if you do the first.

proc sql;
  create table want as
  select * 
  from ( 
    select id,B,D,E 
    from first
    union
    select id,B,D,E
    from second
  ) U
  right join
  select id 
  from third
  on U.id=third.id
;
quit;
Joe
  • 62,789
  • 6
  • 49
  • 67
  • Ok. Let me explain. I want to create the second table from the first table. and then I want to have a clause here to take only those IDs which are there in the third table. is it possible? – user3658367 Apr 08 '16 at 17:09