DATA Step, Macro, Functions and more

Retrieve information

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

Retrieve information

[ Edited ]

 

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   
            
NotePrice  IDNote  IDNotePrice 
AAA1200  45AAA  45AAA1200 
AA+1100  14AAA  14AAA1200 
AA1000  17BBB  17BBB425 
AA-850  45CCC  45CCC200 
A+775  17AA  17AA1000 
A600  19AAA  19AAA1200 
A-500          
BBB+450          
BBB425          
BBB-400          
BB+375          
BB350          
BB-325          
B+300          
B275          
B-250          
CCC+225          
CCC200          
CCC-175          

Accepted Solutions
Solution
4 weeks ago
Contributor
Posts: 36

Re: Retrieve information

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;

View solution in original post


All Replies
Super User
Super User
Posts: 9,227

Re: Retrieve information

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:

Capture.PNG

Contributor
Posts: 33

Re: Retrieve information

thank you very much. I will look at it.
Solution
4 weeks ago
Contributor
Posts: 36

Re: Retrieve information

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 97 views
  • 0 likes
  • 3 in conversation