Desktop productivity for business analysts and programmers

How to convert report period into number of month.

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

How to convert report period into number of month.

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/012016/022016/032016/042016/05
Loan 1200400600300200
Loan 2 500200300400
Loan 3  600100250

 

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    
 12345
 2016/012016/022016/032016/042016/05
Loan 1200400600300200
 2016/022016/032016/042016/05 
Loan 2500200300400 
 2016/032016/042016/05  
Loan 3600100250  

 

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

 

 


Accepted Solutions
Solution
‎05-31-2016 05:42 PM
Trusted Advisor
Posts: 1,052

Re: How to convert report period into number of month.

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;

View solution in original post


All Replies
Solution
‎05-31-2016 05:42 PM
Trusted Advisor
Posts: 1,052

Re: How to convert report period into number of month.

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;

New Contributor
Posts: 2

Re: How to convert report period into number of month.

Thanks TomKari, This worked for me,
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 195 views
  • 0 likes
  • 2 in conversation