求助：DATEPART FUNCTION总是出错

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

我连接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'))

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

关键词：function date PART CTI TIO

日志已经说得很清楚了 ，datepart函数的参数必需是数值型的，可以尝试用ANYDTDTEw.格式input成数值。

xiaobo_mx 发表于 2021-7-22 11:18
日志已经说得很清楚了 ，datepart函数的参数必需是数值型的，可以尝试用ANYDTDTEw.格式input成数值。

谢谢回复。我知道用INPUT转换字符型。但这个 Datepart() 里是一个宏变量b.&evntDate，a.&trigDate.

这两个宏变量代表SQL SEVER里几个表里不同的日期变量，这几个变量如果都是日期数值型，那这段代码就可以运行，如果都是字符型，我可以用INPUT变成数值型。 但是这几个表里的变量有的是数值型有的是字符型（一年前都是日期数值型，这就是为啥这么编宏，但几月前数据库大改动，很多数据变了）

现在我加INPUT，那运行到某个表里的字符型变量，就又出错。我应该怎么改动呢？加个CASE 判断？ 如果 b.&evntDate，a.&trigDate.是数值型，就运行，如果不是就运行另一段加INPUT的代码？怎么加这个CASE 代码？

原代码

%macro nTrigEvent(lbl,n,days,trigKey,trigCrit,trigDate,trigTbl,evntDate,evntTbl,jnCrtr);
        proc sql;
         create table n&n. as
         select x.clientID, max(x.daysTo&days.) as n&n._daysTo&days. label="&lbl.: Days To! &days."
          from (select a.clientID, a.&trigKey., datepart(b.&evntDate.)-datepart(a.&trigDate.) as daysTo&days.,
           abs(datepart(b.&evntDate.)-datepart(a.&trigDate.)) as abs_daysTo&days.
                 from kntt.&trigTbl. as a
                    left join kntt.&evntTbl. as b


代码里的几个宏

%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'))