How to monitor the succession of observations in a longitudanal series?

[Mirisage]

Hi Community,

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.

Category                                                                                          Frequency

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

NPNA                                                                                                   2

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;

run;

data c;

     set b;

     by account_number;

     if first.account_number then flag=0;

     if not first.account_number then flag=1;

     else if last.account_number then flag=2;

run;

Mirisage

[PGStats]

I think you want something like this (Note; no blank lines in the data lines) :

data a;
length Arrears_Band $12;
informat Current_date date9.;
Input Current_date Account_number Arrears_Band :$&;
Format Current_date date9.;
datalines;
........
;

proc sort data=a; by account_number Current_date; run;

data cat(keep=account_number category);
length Category $200;

do until(last.account_number);
do until (last.Arrears_Band);
  set a;
  by account_number Arrears_Band notsorted;
end;
category = catx(" to ", category, Arrears_Band);
end;
run;

proc sql;
create table want as
select Category, count(*) as Frequency from cat group by Category;
select * from want;
quit;

PG

[Mirisage]

Hi PG,

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.

Mirisage

[PGStats]

You're welcome Mirisage, I acquired or refined all those tricks on this forum! - PG