Help using Base SAS procedures

Merging with PROC SQL

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

Merging with PROC SQL

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!


Accepted Solutions
Solution
‎11-19-2012 11:12 PM
PROC Star
Posts: 7,468

Re: Merging with PROC SQL

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;

View solution in original post


All Replies
Solution
‎11-19-2012 11:12 PM
PROC Star
Posts: 7,468

Re: Merging with PROC SQL

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;

Frequent Contributor
Posts: 139

Re: Merging with PROC SQL

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

N/A
Posts: 1

Re: Merging with PROC SQL

Posted in reply to manojinpec

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;

🔒 This topic is solved and locked.

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

Discussion stats
  • 3 replies
  • 282 views
  • 7 likes
  • 4 in conversation