defining the first and last day of the month

[Gieorgie]

I have a question for you. Is there a chance to create a macro something similar to this, but in prco sql in quera where it always selects the first day and the last of the month

%let thismonth=%sysfunc(putn(%sysfunc(today()),yymmn6.));

	where t1.DATE = &thismonth
		and _dly.  between t2.data_from and t2.data_to 
		and &gv_date_dly.  between t3.data_from and t3.data_to 
		and t3.obj_code not in ('G07','N06','N07') 

where we have date from to be recalled from the 1st day of the month and date_to to the last day of the month when the function & thismonth was called

[Gieorgie]

And my date format its 2021-10-26

[Kurt_Bremser]

SAS date values are counts of days with 1960-01-01 as day zero. For comparisons, formats are irrelevant, so you best store raw, unformatted values in macro variables (see Maxim 28).

To get first and last day of the current month, use INTNX:

%let first_day = %sysfunc(intnx(month,%sysfunc(today()),0,b));
%let last_day = %sysfunc(intnx(month,%sysfunc(today()),0,e));

[Gieorgie]

Thanks Kurt ! One question i hope, should I change my code for below. because I run the code it shows me neither errors nor results

where t1.DATA_DANYCH between &first_day and &last_day and t2.data_od = &first_day and t2.data_do = &last_day and t3.data_od = &first_day and t3.data_do = &last_day

[Kurt_Bremser]

Maxim 3: Know Your Data.

Run PROC CONTENTS to make sure your variables contain SAS dates (numeric with date format assigned).

Then inspect the values to see what is present. PROC FREQ can be useful for dates (I use it every time I need to get a picture of stored date values).

If that does not provide a clue, show us values for your dates where the condition does not work as expected.

[Gieorgie]

proc sql;

	create table PolisyEnd as 

	select
	t1.obj_oid
	,t1.PRP_AGREEMENT				 
	,t1.PRP_POLICYNUMBER
	,t1.PRP_SOCIETY_PID
	,t1.PRP_END_DATE 				as PRP_END_DATE
	from 
		cmz.WMDTZDP_BH t1 ,
		where t1.data_od = &first_day. 
        and t1.data_do = &gv_date_dly.
left join 
		(select kontr_id,obj_oid from cmz.BH_D_ZAB_X_ALOK_&thismonth where data_danych = &gv_date_dly.) t2 on t2.obj_oid = t1.obj_oid ;

quit;

%let gv_date_dly=%sysevalf(%bquote('&date_dly.'d));
%let thismonth=%sysfunc(putn(%sysfunc(today()),yymmn10.));
%let first_day = %sysfunc(intnx(month,%sysfunc(today()),0,b));
%let last_day = %sysfunc(intnx(month,%sysfunc(today()),0,e));

Now i got error "ERROR 22-322:Syntax error, one of the following was expected:;, !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, EQT, "  here : where t1.data_od = &first_day. and t1.data_do = &gv_date_dly.

---

---

[Kurt_Bremser]

You have an invalid comma in the FROM clause, and you must move the WHERE clause after the JOIN, which is part of the FROM.

from 
  cmz.WMDTZDP_BH t1
left join 
  (select kontr_id,obj_oid from cmz.BH_D_ZAB_X_ALOK_&thismonth where data_danych = &gv_date_dly.) t2
on t2.obj_oid = t1.obj_oid
where t1.data_od = &first_day. and t1.data_do = &gv_date_dly.

[ballardw]

---

@Gieorgie wrote:

Thanks Kurt ! One question i hope, should I change my code for below. because I run the code it shows me neither errors nor results

where t1.DATA_DANYCH between &first_day and &last_day and t2.data_od = &first_day and t2.data_do = &last_day and t3.data_od = &first_day and t3.data_do = &last_day

---

May need to share some actual values for the variables along with the entire Proc SQL code.

[Gieorgie]

Hi, I changed my code and you have example on above