Selection of the previous date and comparison of the result with the current one

[Gieorgie]

I have this problem because I have a promt set for which picks the report date for me, I select it with where in proc sql. However, it also needs to create a table where I will compare with the previous last date. What function to use for this?

%let gv_date_dly=%sysevalf(%bquote('&date_dly.'d));

this is the code for selecting the actual results,

proc sql;
create table zabcheck as
select 
sum(zab_hip) as hip_curr
,zab_data_spr as date_curr
from _mart.zab_kred 
where zab_data_spr=&gv_date_dly. and zab_hip = 1
group by zab_data_spr
;
quit;

And i would like to create table where

where zab_data_spr=&gv_date_dly.  will be for previous date ( always last day of month)

[Kurt_Bremser]

So you have a macro variable with a date in DATE9. format, and need another macro variable that contains the raw value of the last day of the previous month from that?

[Gieorgie]

Exactly, every day of the report until the last day of the month.

[Kurt_Bremser · Accepted solution]

%let date_dly = 15nov2021;

%let gv_date_dl = %sysfunc(intnx(month,%sysevalf("&date_dly."d),-1,e));

%put &=gv_date_dl;

%put %sysfunc(putn(&gv_date_dl.,yymmdd10.));

The last two statements are for control purposes only.