Merging with PROC SQL

[Benn]

Hey Guys,

I am very puzzled with this procedure. Hope you all can help me with it.

I have 2 data sets. Say A- 100 observations, B- 50 observations. However after merging, they end up with more than 100 observations which I find it very confusing.

PROC SQL NOPRINT;

CREATE TABLE REFDATE_PORTFOLIOS

AS SELECT A.PERMNO, A.DATE, A.CUSIP, A.SHRCD, A.PRC, A.VOL, A.RET, A.SHROUT, A.VWRETD, B.R_RET

FROM PERFECT.CRSP_COMMONSTOCKS as A, PERFECT.MAX_DECILED as B

WHERE A.REFDATE=B.REFDATE and A.CUSIP=B.CUSIP;

I thought because of the criteria, less than 100 observations should be generated in the output.

Thanks for the help!

[art297 · Accepted solution]

If you have multiple records with those same codes, in either file, you could get a similar result.  e.g.:

data CRSP_COMMONSTOCKS;

  input PERMNO CUSIP refdate;

  cards;

1 1 1

2 1 1

3 2 2

4 2 2

5 3 3

6 3 3

7 4 4

;

data MAX_DECILED;

  input cusip refdate;

  cards;

1 1

1 1

1 1

2 2

2 2

;

PROC SQL NOPRINT;

CREATE TABLE REFDATE_PORTFOLIOS

AS SELECT A.PERMNO, /*A.DATE,*/ A.CUSIP/*, A.SHRCD, A.PRC, A.VOL, A.RET, A.SHROUT, A.VWRETD, B.R_RET*/

FROM /*PERFECT.*/CRSP_COMMONSTOCKS as A, /*PERFECT.*/MAX_DECILED as B

WHERE A.REFDATE=B.REFDATE and A.CUSIP=B.CUSIP;

quit;

[manojinpec]

If there are more than one matching values all will match .hence the output would have more rows

[lalmohan]

IF WANT ONLY UNIQUE VALUE FROM TABLE A (WHICH IS NOT PRESENT IN TABLE B) YOU CAN USE THIS.

PROC SQL NOPRINT;

CREATE TABLE REFDATE_PORTFOLIOS

AS SELECT A.PERMNO, /*A.DATE,*/ A.CUSIP/*, A.SHRCD, A.PRC, A.VOL, A.RET, A.SHROUT, A.VWRETD, B.R_RET*/

FROM /*PERFECT.*/CRSP_COMMONSTOCKS as A LEFT JOIN /*PERFECT.*/MAX_DECILED as B

ON A.REFDATE=B.REFDATE and A.CUSIP=B.CUSIP

WHERE B.CUSIP IS NULL;

quit;