topic Understanding of date value with macro variables and any date format in SAS Programming

Understanding of date value with macro variables and any date format

Babloo — Fri, 20 Dec 2019 07:53:34 GMT

I've the data as follows. For the below macro variable (Reportingdate), if I change the value to 2017-03, I'm getting the different value in IR. Could you help me understand why the Format anydtdte7. producing the different values (02FEB2017 and 01MAR2017 for the first two observations) in the Output, for the similar Input.

data test_new;
input PROFITABILITY_FLG $ PREMIUM_DUE_MONTH $ COVERAGE_PERIOD_START_MONTH $;
datalines;
L 2002-06 2001-06
L  2003-06 2003-06
P 2004-06 2002-06
;
run;

%let REPORTINGDATE=20170331;
proc sql;
create table output as select *,CASE   
WHEN PROFITABILITY_FLG= "" then . 
WHEN PROFITABILITY_FLG= "L" then input("&REPORTINGDATE.",anydtdte7.) 
WHEN PROFITABILITY_FLG ne "L" then min(input(PREMIUM_DUE_MONTH,anydtdte7.),input(COVERAGE_PERIOD_START_MONTH,anydtdte7.) )  
END as IR format=date9. from test_new;
run;

Re: Understanding of date value with macro variables and any date format

FreelanceReinh — Fri, 20 Dec 2019 09:44:49 GMT

Hi @Babloo,

The reason is the inappropriate length 7 of informat anydtdte7. for the value "20170331", which has length 8, i.e., the informat converts "2017033" into a SAS date value. Thanks to the notorious flexibility of the ANYDT... informats this conversion is successful: "2017033" can be interpreted as the Julian date value "33rd day of the year 2017", which is 02FEB2017. (Search for "JULIAN" in the documentation of the ANYDTDTE informat.)

So, for "20170331" use yymmn6. if you want to obtain 01MAR2017 (and ignore the "31") or anydtdte8. (or yymmdd8.) for the result 31MAR2017.

Re: Understanding of date value with macro variables and any date format

ed_sas_member — Fri, 20 Dec 2019 09:45:09 GMT

Hi @Babloo

using the anydtdte. format solves the issue. Day is set to the first of the month.

data test_new;
	input PROFITABILITY_FLG $ PREMIUM_DUE_MONTH $ COVERAGE_PERIOD_START_MONTH $;
	datalines;
L 2002-06 2001-06
L  2003-06 2003-06
P 2004-06 2002-06
P 2001-12 2005-06
;
run;

%let REPORTINGDATE=20170331;

proc sql;
	create table output as
	select *,
		   CASE WHEN PROFITABILITY_FLG="" then . 
		   		WHEN PROFITABILITY_FLG="L" then input("&REPORTINGDATE", anydtdte.)
		   		WHEN PROFITABILITY_FLG ne "L" then min(input(PREMIUM_DUE_MONTH, anydtdte.), input(COVERAGE_PERIOD_START_MONTH, anydtdte.) )
		   		ELSE .
		   END as IR format=date9.
	from test_new;
run;