topic Re: Merging with PROC SQL in SAS Procedures

Merging with PROC SQL

Benn — Tue, 20 Nov 2012 03:54:38 GMT

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!

Re: Merging with PROC SQL

art297 — Tue, 20 Nov 2012 04:12:30 GMT

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

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;

Re: Merging with PROC SQL

manojinpec — Tue, 20 Nov 2012 06:30:10 GMT

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

Re: Merging with PROC SQL

lalmohan — Tue, 20 Nov 2012 07:24:22 GMT

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;