topic Re: PROC SQL: How to add a column from one table to another in New SAS User

PROC SQL: How to add a column from one table to another

janon — Wed, 12 Aug 2020 06:59:11 GMT

In this example, I have two tables (Table A and Table B) and I want to make a third table (Table C) which is basically Table A, and the addition of one column from Table B.

i.e.

Table A

Customer_ID	Customer_Name	Customer_Credit_Rating
1	Jane	3
2	Josh	8
3	Jess	8

Table B

Customer_ID	Facility_ID	Security_Type	LVR
1	FAC115	A	88%
2	FAC108	A	75%
8	FAC109	D	80%
9	FAC111	E	90%
10	FAC113	F	80%

Makes Table C

Customer_ID	Customer_Name	Customer_Credit_Rating	LVR
1	Jane	3	88%
2	Josh	8	75%
3	Jess	8	null

How is this done? I have tried a left join but it results in Table C having more records than Table A.

Thanks in advance

Re: PROC SQL: How to add a column from one table to another

PeterClemmensen — Wed, 12 Aug 2020 07:13:36 GMT

data a;
input Customer_ID Customer_Name $ Customer_Credit_Rating;
datalines;
1 Jane 3
2 Josh 8
3 Jess 8
;

data b;
input Customer_ID Facility_ID $ Security_Type $ LVR $;
datalines;
1 FAC115 A 88%
2 FAC108 A 75%
8 FAC109 D 80%
9 FAC111 E 90%
10 FAC113 F 80%
;

proc sql;
   create table want as
   select a.*, b.LVR
   from a left join b
   on a.Customer_ID = b.Customer_ID;
quit;

Re: PROC SQL: How to add a column from one table to another

Kurt_Bremser — Wed, 12 Aug 2020 07:35:11 GMT

Whenever your code does not work as intended, post it and the log from it, so we can point out where you went wrong. Also supply data in usable form (data step with datalines), so we can test our code against exactly the same data that you used.

As has been shown already, the left join works, so you must have mixed up something.