topic Re: EG (Date Formula) in SAS Enterprise Guide

EG (Date Formula)

SASnewbie2 — Fri, 25 May 2018 06:48:31 GMT

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

Re: EG (Date Formula)

Kurt_Bremser — Fri, 25 May 2018 06:47:13 GMT

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

Re: EG (Date Formula)

SASnewbie2 — Fri, 25 May 2018 06:55:12 GMT

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)

Re: EG (Date Formula)

Kurt_Bremser — Fri, 25 May 2018 07:08:59 GMT

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

Re: EG (Date Formula)

SASnewbie2 — Fri, 25 May 2018 07:33:30 GMT

Hi Kurtbremser,

Can i modify from formula below

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

Re: EG (Date Formula)

Patrick — Fri, 25 May 2018 07:41:13 GMT

@SASnewbie2

You can using spaghetti code

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

Re: EG (Date Formula)

SASnewbie2 — Fri, 25 May 2018 07:56:00 GMT

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 ?

Re: EG (Date Formula)

Patrick — Fri, 25 May 2018 08:01:43 GMT

@SASnewbie2 wrote:

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)

Re: EG (Date Formula)

Kurt_Bremser — Fri, 25 May 2018 08:01:50 GMT

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.

Re: EG (Date Formula)

SASnewbie2 — Fri, 25 May 2018 08:07:15 GMT

Hi Patrick & KurtBremser,

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

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

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

Re: EG (Date Formula)

Kurt_Bremser — Fri, 25 May 2018 08:25:01 GMT

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.

Re: EG (Date Formula)

Patrick — Fri, 25 May 2018 08:59:12 GMT

@SASnewbie2 wrote:

Hi Patrick & KurtBremser,

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

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 @Kurt_Bremser 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)

Re: EG (Date Formula)

SASnewbie2 — Fri, 25 May 2018 09:01:55 GMT

Hi Both,

Thanks a lots for your advice 🙂