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 |
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;
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:
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.