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;
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.