Retrieve information

[WilliamB]

Hello,

I would like to recover the price of table 1, to complete my table 2.

In SQL code please.

Thanks for your help.

Table1				Table2				Want
Note	Price			ID	Note			ID	Note	Price
AAA	1200			45	AAA			45	AAA	1200
AA+	1100			14	AAA			14	AAA	1200
AA	1000			17	BBB			17	BBB	425
AA-	850			45	CCC			45	CCC	200
A+	775			17	AA			17	AA	1000
A	600			19	AAA			19	AAA	1200
A-	500
BBB+	450
BBB	425
BBB-	400
BB+	375
BB	350
BB-	325
B+	300
B	275
B-	250
CCC+	225
CCC	200
CCC-	175

[RW9]

As on your last post, put test data in the form of a datastep, we cannot tell structure nor will we type that in to test things.

Something like:

proc sql;
  create table want as
  select a.*,b.price
  from  table2 a
  left join table1 b
  on a.note=b.note;
quit;

If your learning SQL, then its good to read up on the guidance, and help pages such as:

https://www.w3schools.com/sql/

And:

[WilliamB]

thank you very much. I will look at it.

[emrancaan · Accepted solution]

DATA HAVE;
infile cards expandtabs ;
input note $  price;
cards;
AAA	1200
AA+	1100
AA	1000
AA-	850
A+	775
A	600
A-	500
BBB+	450
BBB	425
BBB-	400
BB+	375
BB	350
BB-	325
B+	300
B	275
B-	250
CCC+ 225
CCC	200
CCC- 175

;
run;


DATA HAVE1;
infile cards expandtabs ;
input ID   Note $;
cards;
45	AAA
14	AAA
17	BBB
45	CCC
17	AA
19	AAA
;
Run;


Proc SQl;
Create table want as
Select a.ID,a.Note,Price
FROM have1 a inner join have b on a.note=b.note
where a.id in ( Select ID from have1)
;QUIt;