我连接SQL SERVE提取数据,其中有日期变量,运行代码,总是出现错误
Function DATEPART requires a numeric expression as argument 1.
日志如下
3 %let begDt='01JAN18'd;
4 %let endDt='31DEC18'd;
5
6
7 proc format;
8 value n357_fmt
9 low-15 = 'Within 15 days'
10 15<-high = 'After 15 days'
11 . = 'Not Completed';
NOTE: Format N357_FMT has been output.
12 value n358_fmt
13 low-30 = 'Within 30 days'
14 30<-high = 'After 30 days'
15 . = 'Not Completed';
NOTE: Format N358_FMT has been output.
16 run;
NOTE: PROCEDURE FORMAT used (Total process time):
real time 0.02 seconds
cpu time 0.03 seconds
25
26
27 ************************
28 * Main selection macro *
41 %macro nTrigEvent(lbl,n,days,trigKey,trigCrit,trigDate,trigTbl,evntDate,evntTbl,jnCrtr);
42 proc sql;
43 create table n&n. as
44 select x.clientID, max(x.daysTo&days.) as n&n._daysTo&days. label="&lbl.: Days To
44 ! &days."
45 from
46 (select a.clientID, a.&trigKey., datepart(b.&evntDate.)-datepart(a.&trigDate.)
46 ! as daysTo&days.,
47 abs(datepart(b.&evntDate.)-datepart(a.&trigDate.)) as abs_daysTo&days.
48 from kntt.&trigTbl. as a
49 left join kntt.&evntTbl. as b
50 on %unquote(&jnCrtr.)
51 where &begDt. le datepart(a.&trigDate.) le &endDt. &trigCrit.
52 group by a.clientID, a.&trigKey.
53 having min(abs(datepart(b.&evntDate.)-datepart(a.&trigDate.)))=calculated
53 ! abs_daysTo&days.) as x
54 group by x.clientID
55 order by x.clientID;
56 quit;
57 %mend nTrigEvent;
58
68 %nTrigEvent(lbl=3.4.2,n=342, days=mfpQ, trigKey=AuditDocumentID, trigCrit=%str(and
68 ! a.AuditDocumentID like 'applicationpackets%%' and a.StatusToDisplayName='Submitted'),
69 trigDate=CreatedTimestamp, trigTbl=auditTrail, evntDate=ConsentFormDate,
69 ! evntTbl=mfpQuestionnaire,
70 jnCrtr=%nrstr(a.clientID=b.clientID and WorkflowStatusDisplayName='Submitted'))
ERROR: Function DATEPART requires a numeric expression as argument 1.
ERROR: Expression using subtraction (-) requires numeric types.
ERROR: Function DATEPART requires a numeric expression as argument 1.
ERROR: Expression using subtraction (-) requires numeric types.
ERROR: Function DATEPART requires a numeric expression as argument 1.
ERROR: Expression using subtraction (-) requires numeric types.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.08 seconds
cpu time 0.01 seconds
改了很多次,都失败
Datepart 后变变量应该是日期,但从SQL出来的日期好像不是DATE FORMAT。我加了INPUT,但又出错。 因为变量好像是宏
代码里的几个宏
%nTrigEvent(lbl=3.4.2,n=342, days=mfpQ, trigKey=AuditDocumentID, trigCrit=%str(and a.AuditDocumentID like 'applicationpackets%%' and a.StatusToDisplayName='Submitted'),
trigDate=CreatedTimestamp, trigTbl=auditTrail, evntDate=ConsentFormDate, evntTbl=mfpQuestionnaire,
jnCrtr=%nrstr(a.clientID=b.clientID and WorkflowStatusDisplayName='Submitted'))
%nTrigEvent(lbl=3.5.27,n=3527, days=advisoryLetter, trigKey=atpFormID, trigCrit=%str(and ATPTypeID='atptypes/-2'),
trigDate=waiverServicesEffectiveDate, trigTbl=vwatpForm, evntDate=LetterDate, evntTbl=dewsLetter,
jnCrtr=%nrstr(a.clientID=b.clientID and dewsLetterTypeID='dewslettertypes/-2' and b.WorkflowStatus='Submitted'))