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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.