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

Super Contributor
Posts: 338

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

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

Posts: 5,529

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

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

PG
Super Contributor
Posts: 338

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

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