Number of Days Calculation

[CWilson4815]

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

[Kurt_Bremser]

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

[CWilson4815]

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, *.

[Kurt_Bremser · Accepted solution]

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

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

[CWilson4815]

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.

[Kurt_Bremser]

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

[CWilson4815]

School boy stuff, thanks a lot Kurt.