05-28-2012 05:48 PM
I have a data set like in the attached.
Q. I need to monitor how each customer has moved between different delinquency categories. Finally, I should get a freq table like this.
NPNA to NPNA 1
current to 30-60 to current to 1-30 to 30-60 to 60-90 to 90+ to NPNA 1
30-60 to NPNA 1
I have tried upto creating a flag variable through "by group processing" having read some materials but it doesn't seem to be the correct direction. Your help is highly appreciated.
Proc sort data = a out =b;
by Account_number Current_date;
if first.account_number then flag=0;
if not first.account_number then flag=1;
else if last.account_number then flag=2;
05-28-2012 06:31 PM
I think you want something like this (Note; no blank lines in the data lines) :
length Arrears_Band $12;
informat Current_date date9.;
Input Current_date Account_number Arrears_Band :$&;
Format Current_date date9.;
proc sort data=a; by account_number Current_date; run;
data cat(keep=account_number category);
length Category $200;
do until (last.Arrears_Band);
by account_number Arrears_Band notsorted;
category = catx(" to ", category, Arrears_Band);
create table want as
select Category, count(*) as Frequency from cat group by Category;
select * from want;
05-29-2012 11:13 AM
Your code is working nicely. Thank you so much for this.
The code stimulated me to explore new things such as “Ampersand Modifier”, “do until”, “catx” function, and creating a table using proc sql, “from” the same table. I am further exploring on all these.
Thanks again PG.