CODEWITHSUNDEEP
sas
Ben
Ben

Reputation: 489

Dates and Between statement

I am using SAS E.G. 7.1

I have the following code:

data time_dim_monthly;

    do i = 0 to 200;
        index_no = i;
        year_date = year(intnx('month','01JAN2008'd,i));
        month_date = month(intnx('month','01JAN2008'd,i));
        SOM = put(intnx('month', '01JAN2008'd, i, 'b'),date11.) ; 
        EOM = put(intnx('month', '01JAN2008'd, i, 'e'),date11.) ; 
        days_in_month = INTCK('day',intnx('month', '01JAN2008'd, i, 'b'),
                        intnx('month', '01JAN2008'd, i, 'e')); 
        output;
    end;
run;

followed by 

proc sql;
    create table calendar as 
        select year_date, month_date, index_no, put(today(),date11.) as todays_dt, som, eom
            from time_dim_monthly
                where put(today(),date11.) between som and eom
/*or datepart((INTNX('month',today(),-1)) between som and eom)*/
        order by index_no
                ;
quit;

The output looks like this:

year_date   month_date  index_no    todays_dt   SOM EOM
2008    10  9   31-MAY-2017 01-OCT-2008 31-OCT-2008
2009    10  21  31-MAY-2017 01-OCT-2009 31-OCT-2009
2010    10  33  31-MAY-2017 01-OCT-2010 31-OCT-2010
2011    10  45  31-MAY-2017 01-OCT-2011 31-OCT-2011
2012    10  57  31-MAY-2017 01-OCT-2012 31-OCT-2012
2013    10  69  31-MAY-2017 01-OCT-2013 31-OCT-2013
2014    10  81  31-MAY-2017 01-OCT-2014 31-OCT-2014
2015    10  93  31-MAY-2017 01-OCT-2015 31-OCT-2015
2016    10  105 31-MAY-2017 01-OCT-2016 31-OCT-2016
2017    5   112 31-MAY-2017 01-MAY-2017 31-MAY-2017
2017    10  117 31-MAY-2017 01-OCT-2017 31-OCT-2017
2018    5   124 31-MAY-2017 01-MAY-2018 31-MAY-2018
2018    10  129 31-MAY-2017 01-OCT-2018 31-OCT-2018
2019    5   136 31-MAY-2017 01-MAY-2019 31-MAY-2019
2019    10  141 31-MAY-2017 01-OCT-2019 31-OCT-2019
2020    5   148 31-MAY-2017 01-MAY-2020 31-MAY-2020
2020    10  153 31-MAY-2017 01-OCT-2020 31-OCT-2020
2021    5   160 31-MAY-2017 01-MAY-2021 31-MAY-2021
2021    10  165 31-MAY-2017 01-OCT-2021 31-OCT-2021
2022    5   172 31-MAY-2017 01-MAY-2022 31-MAY-2022
2022    10  177 31-MAY-2017 01-OCT-2022 31-OCT-2022
2023    5   184 31-MAY-2017 01-MAY-2023 31-MAY-2023
2023    10  189 31-MAY-2017 01-OCT-2023 31-OCT-2023
2024    5   196 31-MAY-2017 01-MAY-2024 31-MAY-2024

While I'd expected that it would only give me one line:

2017 5 112 31-MAY-2017 01-MAY-2017 31-MAY-2017

Would appreciate help in understanding why this is happening.

Thank you

Upvotes: 0

Views: 149

Answers (1)

Joe
Joe

Reputation: 63424

This is your mistake:

SOM = put(intnx('month', '01JAN2008'd, i, 'b'),date11.) ; 
EOM = put(intnx('month', '01JAN2008'd, i, 'e'),date11.) ; 
where put(today(),date11.) between som and eom

put creates a character variable. You shouldn't really use between with character variables unless you really know what you're doing (it will compare in alphabetical order).

Use numeric variables. Get rid of the put. Instead use a format statement to make the variables look nice, but still be numeric.

SOM = intnx('month', '01JAN2008'd, i, 'b') ; 
EOM = intnx('month', '01JAN2008'd, i, 'e') ; 
format som eom date11.;

later

where today() between som and eom

Upvotes: 1

Related Questions