topic Re: PROC SQL CREATE TABLE in SAS Procedures

PROC SQL CREATE TABLE

deleted_user — Wed, 14 Jan 2009 00:27:15 GMT

Hi, I am trying to join two tables. Table A has fewer observations than Table B.
I only want the additional information from table B added to Table A.
I am getting, according to the log, 1 fewer observation in the new table named
CHARTER than in the original table I wanted to add an additional field to from Table B. Why?

PROC SQL;
CREATE TABLE CHARTER AS
SELECT
A.COCODE,
A.COUNTY,
A.DISTRICT,
A.ENROLLMENT,
A.SCHOOL,
A.SCHOOL_CODE,
A.TYPE,
B.CHARTER,
B.DIST_CODE,
B.PHYS_CITY
FROM CHECK A INNER JOIN KINDER B
ON (A.SCHOOL_CODE=B.SCH_CODE);
QUIT;

Re: PROC SQL CREATE TABLE

Patrick — Wed, 14 Jan 2009 10:30:46 GMT

.....
from check a LEFT JOIN kinder B
.....

A INNER JOIN gives you only the rows with common keys in both tables.
If table A has a row where the key doesn't match with table B then this row won't be part of the result table.

Use a left join and you won't loose rows from table A (but of course: one row in the result table will have missings for all vars coming from table B).

HTH
Patrick

Re: PROC SQL CREATE TABLE

deleted_user — Wed, 14 Jan 2009 16:26:27 GMT

Thanks! That does help!