Desktop productivity for business analysts and programmers

EG (Date Formula)

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

EG (Date Formula)

[ Edited ]

Hi All,

 

Appreciate your advice what function or formula should i input if i would like to get a yesterday date in EG to show in the Table Title 

 

In excel formula is  (Today()-1), how about in SAS ? i have tried this %sysfunc(today()-1, ddmmyy10) but it doesn't work


Accepted Solutions
Solution
4 weeks ago
Respected Advisor
Posts: 4,679

Re: EG (Date Formula)

Posted in reply to SASnewbie2

@SASnewbie2 wrote:

SAS.png

Hi Partick,

 

Can i copy & past into the table titles as shown above ? I

 

try to run it, but it doesn't seem work. Am i miss out something ? 


 

Remove the %PUT statement. Below the bit which you need to pass in for your title

%sysfunc(sum(%sysfunc(today()),-1), nldate20. -l)

View solution in original post


All Replies
Super User
Posts: 9,914

Re: EG (Date Formula)

Posted in reply to SASnewbie2

It's the same, literally:

data _null_;
format date yymmddd10.;
date = today();
put date=;
date = today() - 1;
put date=;
run;

Log:

27         data _null_;
28         format date yymmddd10.;
29         date = today();
30         put date=;
31         date = today() - 1;
32         put date=;
33         run;

date=2018-05-25
date=2018-05-24
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 14

Re: EG (Date Formula)

Posted in reply to KurtBremser

Hi Kurtbremser, 

 

Thanks for your advice. However, I would like to have the yesterday date in the Table title. 

 

Appreciate you could advise how should i revise the formula below .

 

Sales Revenue as at %TRIM(%QSYSFUNC(DATE(), NLDATE20.)) to become Sales Revenue as at 24.05.2018 (One day before)

 

 

Super User
Posts: 9,914

Re: EG (Date Formula)

[ Edited ]
Posted in reply to SASnewbie2

When I need to more that a simple assignment, I use to create macro variables in a data step for easier readable code:

data _null_;
call symput('yesterday',trim(put(today()-1,nldate20.)));
run;

title "SASHELP.CLASS at &yesterday";

proc print data=sashelp.class;
run;

Which results in

         SASHELP.CLASS at 24. Mai 2018          

Obs    Name       Sex    Age    Height    Weight

  1    Alfred      M      14     69.0      112.5
  2    Alice       F      13     56.5       84.0
  3    Barbara     F      13     65.3       98.0
  4    Carol       F      14     62.8      102.5
  5    Henry       M      14     63.5      102.5
  6    James       M      12     57.3       83.0

 

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

Re: EG (Date Formula)

Posted in reply to KurtBremser

Hi Kurtbremser,

 

Can i modify from formula below 

 

%TRIM(%QSYSFUNC(DATE(), NLDATE20.)) to yesterday date 

Respected Advisor
Posts: 4,679

Re: EG (Date Formula)

Posted in reply to SASnewbie2

@SASnewbie2

You can using spaghetti code

%put %sysfunc(sum(%sysfunc(today()),-1), nldate20. -l);
Occasional Contributor
Posts: 14

Re: EG (Date Formula)

SAS.png

Hi Partick,

 

Can i copy & past into the table titles as shown above ? I

 

try to run it, but it doesn't seem work. Am i miss out something ? 

Solution
4 weeks ago
Respected Advisor
Posts: 4,679

Re: EG (Date Formula)

Posted in reply to SASnewbie2

@SASnewbie2 wrote:

SAS.png

Hi Partick,

 

Can i copy & past into the table titles as shown above ? I

 

try to run it, but it doesn't seem work. Am i miss out something ? 


 

Remove the %PUT statement. Below the bit which you need to pass in for your title

%sysfunc(sum(%sysfunc(today()),-1), nldate20. -l)
Super User
Posts: 9,914

Re: EG (Date Formula)

Posted in reply to SASnewbie2

Oh, you need it for pointy-clicky.

 

%put is a macro statement that writes to the log; @Patrick just used it so you could see the result.

Use

%trim(%sysfunc(sum(%sysfunc(today()),-1), nldate20.))

in your title bar.

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

Re: EG (Date Formula)

[ Edited ]
Posted in reply to KurtBremser

Hi Patrick & KurtBremser, 

 

It works now, thanks a lots for your advice & assistance Smiley Happy

 

Would you mind to explain why need to include the Sum( function

 

%sysfunc(sum(%sysfunc(today()),-1), nldate20.))

 

 

Super User
Posts: 9,914

Re: EG (Date Formula)

Posted in reply to SASnewbie2

When working with the macro facility, always keep in mind it is a preprocessor for creating programs, and it only knows the datatype text, and by itself is incapable of doing calculations.

today() - 1

is just text for it, and %sysfunc accepts as its first argument only a single data step function call; the additional %sysfunc(sum()) takes care of the calculation, which would not be possible otherwise.

An alternative is the use of %eval, as in

%trim(%sysfunc(putn(%eval(%sysfunc(today())-1), nldate20.)))

My personal preference is to never use more than one %sysfunc in one line, as you can see how it tends to clutter up the code and make it harder to see the logic behind the code. Therefore my preferred method to unload such calculations into a preceding data step.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Respected Advisor
Posts: 4,679

Re: EG (Date Formula)

Posted in reply to SASnewbie2

@SASnewbie2 wrote:

Hi Patrick & KurtBremser, 

 

It works now, thanks a lots for your advice & assistance Smiley Happy

 

Would you mind to explain why need to include the Sum( function

 

%sysfunc(sum(%sysfunc(today()),-1), nldate20.))

 

 


 

You've got already all the explanation from @KurtBremser but to say it in other words:

%sysfunc() allows you to use a single SAS data step function on SAS macro level.

We need to do two things: Call the today() function and substract a value from another value. So we need two %sysfunc() AND we need to use also a SAS function  - SUM() - for this substraction.

 

Another option would have been to use the SAS calendar function INTNX() instead of SUM() to shift the SAS date value. Something like:

%sysfunc(intnx(day,%sysfunc(today()),-1), nldate20. -l)
Occasional Contributor
Posts: 14

Re: EG (Date Formula)

Hi Both, 

 

Thanks a lots for your advice Smiley Happy

☑ This topic is solved.

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

Discussion stats
  • 12 replies
  • 217 views
  • 2 likes
  • 3 in conversation