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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.