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!
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;
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;
If there are more than one matching values all will match .hence the output would have more rows
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.