Desktop productivity for business analysts and programmers

Number of Days Calculation

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Number of Days Calculation

[ Edited ]

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


Accepted Solutions
Solution
‎11-23-2017 11:58 AM
Super User
Posts: 10,574

Re: Number of Days Calculation

Posted in reply to CWilson4815

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

(DATDIF( t2.MODIFIEDDATE,"&mydate."d,'ALT/ALT'))
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 10,574

Re: Number of Days Calculation

Posted in reply to CWilson4815

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')
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 11

Re: Number of Days Calculation

Posted in reply to KurtBremser

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, *.
Solution
‎11-23-2017 11:58 AM
Super User
Posts: 10,574

Re: Number of Days Calculation

Posted in reply to CWilson4815

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

(DATDIF( t2.MODIFIEDDATE,"&mydate."d,'ALT/ALT'))
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 11

Re: Number of Days Calculation

Posted in reply to KurtBremser

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.
Super User
Posts: 10,574

Re: Number of Days Calculation

Posted in reply to CWilson4815

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 11

Re: Number of Days Calculation

Posted in reply to KurtBremser

School boy stuff, thanks a lot Kurt.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 258 views
  • 0 likes
  • 2 in conversation