topic Re: Number of Days Calculation in SAS Enterprise Guide

Number of Days Calculation

CWilson4815 — Thu, 23 Nov 2017 13:20:44 GMT

Hi guys,

I have a computed column I'm using with a query builder in SAS EG 7.1 that takes the amount of days between my field called t2.MODIFIEDDATE and today.

It currently reads as follows and works fine:

DATDIF( t2.MODIFIEDDATE,DATE(),'ALT/ALT')

However, I would like to be able to have today's date replaced with a date that is a variable that can be typed in via prompt so I can "backdate" it to act as if it were to be run yesterday, for example.

Do you know how I can go about this?

Thanks,

Chris

Re: Number of Days Calculation

Kurt_Bremser — Thu, 23 Nov 2017 13:27:32 GMT

In Enterprise Guide, add a User Prompt (icon right next to the server list, blue circle with digits 1,2,3). Define it as a date prompt. In the code, you have access to the result via a macro variable that has the same name as the prompt.

eg if you named your prompt "mydate", your code will then look like

DATDIF( t2.MODIFIEDDATE,&mydate.,'ALT/ALT')

Re: Number of Days Calculation

CWilson4815 — Thu, 23 Nov 2017 13:38:38 GMT

Thanks Kurt, however it is returning the following SYNTAX error:

(DATDIF( t2.MODIFIEDDATE,&mydate.,'ALT/ALT')) LABEL="DAYSSINCELASTDECISION" AS DAYSSINCELASTDECISION,

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, 
              a missing value, ), BTRIM, INPUT, PUT, SUBSTRING, USER.  

ERROR 22-322: Syntax error, expecting one of the following: a name, *.

Re: Number of Days Calculation

Kurt_Bremser — Thu, 23 Nov 2017 13:43:16 GMT

Oh, missed that the date will come in standard SAS notation. Add double quotes and a d:

(DATDIF( t2.MODIFIEDDATE,"&mydate."d,'ALT/ALT'))

Re: Number of Days Calculation

CWilson4815 — Thu, 23 Nov 2017 13:51:05 GMT

Looks like we're nearly there but I've got an error as follows:

((DATDIF( t2.MODIFIEDDATE,"&mydate."d,'ALT/ALT'))) LABEL="DAYSSINCELASTDECISION" AS DAYSSINCELASTDECISION,
WARNING: Apparent symbolic reference MYDATE not resolved.
ERROR: Invalid date/time/datetime constant "&mydate."d.

Re: Number of Days Calculation

Kurt_Bremser — Thu, 23 Nov 2017 13:55:21 GMT

After you define a prompt, you have to attach it to the project node in question via the Properties context menu.

Re: Number of Days Calculation

CWilson4815 — Thu, 23 Nov 2017 16:58:20 GMT

School boy stuff, thanks a lot Kurt.