How to change date format in macro &let

[Gieorgie]

I have a macro that I want to put in the where query, but I don't find the results, I think because my dates have the same format as in the picture below, how to transform the first_day macro to select the first day of the month, e.g. from the current month

%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))

proc sql;

	create table PolisyEnd as 

	select
	datepart(t1.data_danych)  as DATA_DANYCH format yymmdd10.
	,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 as t1
        where datepart(t1.data_danych) = &first_day. 
		and datepart(t1.data_danych) = &gv_date_dly.
;
quit;

When I leave the where gv_data condition alone, it will find the dates for me, but as it adds first_day. I have a problem and it shows me empty results. How do I change macro & let first_day to show this date format

[Kurt_Bremser]

A date is a date is a date. For comparisons, the format is IRRELEVANT(!!), as the raw values are compared.

So you either have something that isn't really a SAS date, or you have no matches.

What does PROC CONTENTS say about 

data_danych

in your dataset? And what values appear when you run PROC FREQ on that variable?

[Gieorgie]

3
DATA_DANYCH Num 8 DATETIME20. DATETIME20. DATA_DANYCH

[Kurt_Bremser]

See an adapted example of my code:

data have;
input date1 :e8601dt19. date2 :e8601dt19.;
format date1 date2 e8601dt19.;
datalines;
2021-10-26T05:23:22 2021-10-01T01:02:03
;

%let date_dly = 26oct2021;
%let gv_date_dly = %sysevalf("&date_dly."d);
%let first_day = %sysfunc(intnx(month,%sysfunc(today()),0,b));

proc sql;
select * from have
where datepart(date1) = &gv_date_dly. and datepart(date2) = &first_day.;
quit;

But you still need to fix the issue (in your code) that a single variable can't be equal to two different values at the same time.

[Tom]

---

@Gieorgie wrote:
3
DATA_DANYCH Num 8 DATETIME20. DATETIME20. DATA_DANYCH

---

So you need to compare the values in that variable to a DATETIME value (number of seconds) not a DATE value (number of days).

[yabwon]

What is the "date_dly" ? Is it standard 26oct2021 ?

Single quotes in the:

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

stops date_dly from resolving. If you run:

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

%put _user_;

you will see an error in the log and null value.

(off topic, the  "DATA_DANYCH" - sounds Polish, maybe write on PolSUG group [ https://communities.sas.com/polsug ]?)

Best

Bart

[Gieorgie]

data_dly is standard 26oct2021 . And when i put on where query i got result, but i need find range from dataset. Always start date its first day of current month . And i have problem with macro &First_day

[Kurt_Bremser]

This condition

where datepart(t1.data_danych) = &first_day. 
		and datepart(t1.data_danych) = &gv_date_dly.

will only be true if both macro variables contain the same value, and it matches a dataset value.

[Kurt_Bremser]

See a quick, simplified example:

data have;
input date1 :yymmdd10. date2:yymmdd10.;
format date1 date2 yymmdd10.;
datalines;
2021-10-26 2021-10-01
;

%let date_dly = 26oct2021;
%let gv_date_dly = %sysevalf("&date_dly."d);
%let first_day = %sysfunc(intnx(month,%sysfunc(today()),0,b));

proc sql;
select * from have
where date1 = &gv_date_dly. and date2 = &first_day.;
quit;

Once you have corrected the single to double quotes and checked the issue I mentioned in the other post, it comes down to which dates you get from the DATEPART function in your condition.

If date_danych is already a date, then using the DATEPART function will always result in a date of 1960-01-01 (or 1959-12-31 for dates before 1960), as DATEPART is intended to convert counts of seconds to counts of days (roughly, doing a division by 86400).