Date:         Sun, 1 Jan 2012 09:00:48 -0500
Reply-To:     Yashodhan Pande <shaunak.adgaonkar@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Yashodhan Pande <shaunak.adgaonkar@GMAIL.COM>
Subject:      SQL table creation help needed
Content-Type: text/plain; charset=ISO-8859-1

Please find below my tables

Table1
Id1 Price1
12 100
24 300
45 400
72 882
Table2
Id2 Price2
130 500
131 600
132 700
135 44
Table3
Id3 Price3
9 900
24 1100
114 567
Table4
Id4 Price4
13 20
45 52
56 63
777 784
Common  Table
Name Key1 Key2 Key3 Key4
Insurance 12 130 9 13
Catastrophe 32 131 10 45
Risk 24 132 11 56
Man Made 45 133 12 68
Natural 72 134 114 777
Health 74 135 128 754



I want report something like this

Type Price1 Price2 Price3 Price4
Insurance Val.table1 Val.table2 Val.table3 Val.table4
Catastrophe Val.table2 Val.table4
Risk Val.table1 Val.table2 Val.table3 Val.table4
Man Made Val.table1 Val.table3
Natural Val.table1 Val.table2 Val.table3 Val.table4
Health Val.table2 Val.table4


Now I am thinking in following direction

proc sql;

create table temp1 as select a.price1,b.name from table1 left join
comm_table b on a.id1=b.key1 where b.name in ('Insurance,Risk,Man Made,Natural);

quit;

proc sql;

create table temp2 as select a.price2,b.name from tabel2 left join
comm_table b on a.id2=b.key2 where b.name in
('Insurance,Catastrophe,Risk,Health);

quit;



proc sql;

create table temp3 as select a.price3,b.name from table3 left join
comm_table b on a.id3=b.key3 where b.name in ('Insurance,Risk,Natural);

quit;



proc sql;

create table temp4 as select a.price4,b.name from table4 left join
comm_table b on a.id4=b.key 4where b.name in
('Insurance,Catastrophe,Risk,Natural);

quit;

and then joining these 4 tables. Can this be done in a single query ??? any
help is really appreciated