DATA Step, Macro, Functions and more

return a 3 month increment date column based on the date column that is just before analysis date,

Reply
Frequent Contributor
Posts: 133

return a 3 month increment date column based on the date column that is just before analysis date,

%let analysis_date = "19dec2011"d;
Data first_cpn_dt;
input first_cpn_dt:mmddyy10.;
cards;
05/01/2010
06/15/2010
01/01/2007
07/16/2010
07/26/2010
09/01/2006
11/26/2010
12/01/2010
12/11/2009
12/14/2009
12/15/2010
;

data temp;
set first_cpn_dt;
i=1;
do until(t_incrt>&analysis_date);
t_incrt=intnx('month', first_cpn_dt, mdy(month(first_cpn_dt)+3*i, day(first_cpn_dt), year(first_cpn_dt)),s);
i+1;

output
end;
pre=mdy(month(first_cpn_dt)+3*(i-1), day(first_cpn_dt), year(first_cpn_dt));
format pre mmddyy10.;
run;

My logic is that use the do until the loop to find the date right after the analysis date then use its previous date.

but the intnx ran out very quickly in months, and it does not increment year by 1 when month is 13 etc.

how do I fix it?

appreciate it!

Super Contributor
Posts: 1,636

return a 3 month increment date column based on the date column that is just before analysis date,

Hi Rick,

there should be a ':' after 'output' in your code.

%let analysis_date = "19dec2011"d;

Data first_cpn_dt;

input first_cpn_dt:mmddyy10.;

format first_cpn_dt mmddyy10.;

cards;

05/01/2010

06/15/2010

01/01/2007

07/16/2010

07/26/2010

09/01/2006

11/26/2010

12/01/2010

12/11/2009

12/14/2009

12/15/2010

;

data temp(drop=i);

set first_cpn_dt;

format t_incrt pre mmddyy10.;

do i=1 to 999 ;

t_incrt=intnx('month',first_cpn_dt,3*i);

if t_incrt>&analysis_date then do;

pre=intnx('month',t_incrt,-3);

leave;

end; end;

proc print;run;

                              first_

                   Obs        cpn_dt       t_incrt           pre

                     1    05/01/2010    02/01/2012    11/01/2011

                     2    06/15/2010    03/01/2012    12/01/2011

                     3    01/01/2007    01/01/2012    10/01/2011

                     4    07/16/2010    01/01/2012    10/01/2011

                     5    07/26/2010    01/01/2012    10/01/2011

                     6    09/01/2006    03/01/2012    12/01/2011

                     7    11/26/2010    02/01/2012    11/01/2011

                     8    12/01/2010    03/01/2012    12/01/2011

                     9    12/11/2009    03/01/2012    12/01/2011

                    10    12/14/2009    03/01/2012    12/01/2011

                    11    12/15/2010    03/01/2012    12/01/2011

Linlin

Frequent Contributor
Posts: 133

return a 3 month increment date column based on the date column that is just before analysis date,

So, I guess intnx does take care of the year increments when month is over 12?

Super User
Posts: 5,256

return a 3 month increment date column based on the date column that is just before analysis date,

Intnx works almost to eternity...

Data never sleeps
Ask a Question
Discussion stats
  • 3 replies
  • 206 views
  • 3 likes
  • 3 in conversation