INTNX- problem

[Daniel1027]

Hello eveyrone,

I was trying to implement this code. unforutnately, sas does not recognize my intnx. what shoud i do?

Best wishes,

Sincerely Daniel.

ROC SQL;
CREATE TABLE crsp_m2
AS SELECT A.*, B.dlret,
sum(1,ret)*sum(1,dlret)-1 as retadj "adjusted return for delisting",
abs(a.prc*a.shrout) as MEq 'Market Value of Equity'
FROM CRSP_M AS A LEFT JOIN tmp1.msedelist (where=(missing(dlret)=0 ) ) AS b
ON a.permno=b.permno AND
intnx('month',a.date,0,'E')=intnx('month',b.DLSTDT,0,'E')
ORDER BY date, permco, MEq;
QUIT;

[Reeza · Accepted solution]

Post your log or explain how it doesn't work please.

I think your error is In how you want to name the variable with spaces. If you want spaces in a variable name the you wrap it in quotes and add an N afterwards. You're missing the N from both variable names. 

[Daniel1027]

DATA crsp_m (KEEP=permco permno date ticker ncusip shrcd exchcd prc ret retx shrout cfacpr cfacshr exchcd);
MERGE tmp1.mse tmp1.msf;
BY permno date;
RUN;
PROC SORT DATA=crsp_m OUT=crsp_m;
BY date permno;
RUN;
PROC SQL;
CREATE TABLE crsp_m2
AS SELECT A.*, B.dlret,
sum(1,ret)*sum(1,dlret)-1 as retadj "adjusted return for delisting",
abs(a.prc*a.shrout) as MEq 'Market Value of Equity'
FROM CRSP_M AS A LEFT JOIN tmp1.msedelist (where=(missing(dlret)=0 ) ) AS b
ON a.permno=b.permno AND
intnx('month',a.date,0,'E')=intnx('month',b.DLSTDT,0,'E')
ORDER BY date, permco, MEq;
QUIT;

this is the entire code i am trying.
LOG:
NOTE: Invalid (or missing) arguments to the ABS function have caused the function to return a
missing value.
NOTE: Invalid argument 2 to function INTNX. Missing values may be generated.

[Astounding]

Have you confirmed that your date variables are actually dates, and not date-times?  Date-time values would likely be too large for INTNX to handle.

[Daniel1027]

i am novice to sas. how do i confirm? i am dealing with 10gb dataset. one thing: why is abs not working?

[Reeza]

The ABS() function works fine, but you have missing values for one of the variables in the function and the log is warning you that in this case you will get a missing value returned.