How to convert report period into number of month.

[Jamal1]

I am new SAS user and trying to create an additional Month_No variable by using Report_Month (Character Variable) . I have 3 differnt SAS data sets with common variables (Loan, Report_Month & Chargeoff$). When I apend datasets in SAS and do pivot table of SAS output, I get the look below which is correct using the Report_Month variable (Charge off for each loan show up in the month they occure).

	Report_Month
	2016/01	2016/02	2016/03	2016/04	2016/05
Loan 1	200	400	600	300	200
Loan 2		500	200	300	400
Loan 3			600	100	250

What I want to do is  assign new variable (Month_No) 1 to the first charge off Report_Month and Month_No 2 to 2nd charge off Report_Month and so on get the following look below.

	Month_no
	1	2	3	4	5
	2016/01	2016/02	2016/03	2016/04	2016/05
Loan 1	200	400	600	300	200
	2016/02	2016/03	2016/04	2016/05
Loan 2	500	200	300	400
	2016/03	2016/04	2016/05
Loan 3	600	100	250

With my little knowledge, I have tried the Retain and Accumulating statements but none is working for me. Any help will be greatly appreciated.

Jamal

[TomKari · Accepted solution]

What you're trying to do is a little tricky for a beginner.

First, I strongly recommend you convert any dates that you use into SAS date variables, which are numeric. That's what the input function does, creating a date of the first of the month.

Once you have dates, you can use intck to get pretty much any kind of interval you want.

data want;

set have;

retain Beginning_Date;

Report_Month_Num = input(strip(Report_Month)||"/01", yymmdd10.);

format Report_Month_Num date9.;

if _n_ = 1 then Beginning_Date = Report_Month_Num;

Month_No = intck('Month', Beginning_Date, Report_Month_Num) + 1;

drop Beginning_Date;

run;

[Jamal1]

Thanks TomKari, This worked for me,