CODEWITHSUNDEEP
sas
Andre
Andre

Reputation: 9

How to count months per year between 2 dates in SAS

A quick SAS question, I have a dataset like this:

   Case | Start_date |  End_date 
    001 |    2014/12 |   2016/01
    002 |    2013/10 |   2015/12
    ...

Based on the Start_date and End_date, I would like to get the number of months per year between two dates. 

   Case | Start_date |  End_date | 2012 | 2013 | 2014 | 2015 | 2016
    001 |    2014/12 |   2016/01 |   0  |   0  |   1  |  12  |  1
    002 |    2013/10 |   2015/12 |   0  |   3  |  12  |  12  |  0
    ...

Any ideas in SAS?

Kindly help in providing the solution.

Best

Upvotes: 0

Views: 656

Answers (2)

data _null_
data _null_

Reputation: 9109

SAS has functions that make working with dates relatively easy. This example uses INTNX to define ranges for each year and INTCK to count the months from start to end by year.

data have;
   input case:$3. (start end)(:yymmdd.);
   format start end yymms.;
   yrs = intck('year',start,end);
   startOfRange = start;
   do i = 0 to yrs;
      endOfRange = min(intnx('year',startOfRange,0,'e'),end);
      months = intck('month',startOfRange,endOfRange) + 1;
      output;
      startOfRange = endOfRange+1;
      end;
   format startOfRange endOfRange yymmdds10.;
   cards;
001 2012/12/01 2016/01/01
002 2013/10/01 2015/01/01
;;;;
   run;
proc print;
   run;
proc transpose data=have out=wide(drop=_name_) prefix=Y;
   by case start end;
   var months;
   id startOfRange;
   format startOfRange year4.;
   run;
proc print;
   run;

enter image description here

Upvotes: 0

vknowles
vknowles

Reputation: 784

Try this. I made some simplifying assumptions, including the use of character input data. This should work for an arbitrary range of years.

data have;
    case="001"; start="2012/12"; end="2016/01"; output;
    case="002"; start="2013/10"; end="2015/12"; output;
run;

* Sorting is necessary for the PROC TRANSPOSE later.;
proc sort data=have;
    by case start end;
run;

data two;
    set have;

    * For each date range, extract the starting and ending
    * year and month.;
    startyear=input(substr(start,1,4),4.0);
    startmonth=input(substr(start,6,2),2.0);
    endyear=input(substr(end,1,4),4.0);
    endmonth=input(substr(end,6,2),2.0);

    * For each year in range, calculate the number of months.;
    do year=startyear to endyear;
        months=0;
        * Start with the month of the first year or January.;
        if year=startyear then mstart=startmonth;
         else mstart=1;
        * End with the month of the last year or December.;
        if year=endyear then mstop=endmonth;
         else mstop=12;

        * Calculate the number of months in the year.;
        months=mstop-mstart+1;

        * Create a row for each year in the range.;
        output;

    end;

    keep case start end year months;
run;

* Transpose the data to create a variable for each year 
* used in any range.;
proc transpose data=two out=want;
    by case start end;
    id year;
run;

Upvotes: 1

Related Questions