topic Re: Retrieve information in SAS Programming

Retrieve information

WilliamB — Mon, 30 Apr 2018 13:15:34 GMT

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

Re: Retrieve information

RW9 — Mon, 30 Apr 2018 13:21:14 GMT

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:

Re: Retrieve information

WilliamB — Mon, 30 Apr 2018 13:23:51 GMT

thank you very much. I will look at it.

Re: Retrieve information

emrancaan — Mon, 30 Apr 2018 13:32:31 GMT

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;