topic Re: How to monitor the succession of observations in a longitudanal series? in SAS Procedures

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

Mirisage — Mon, 28 May 2012 21:48:36 GMT

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

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

PGStats — Mon, 28 May 2012 22:31:28 GMT

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;

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

Mirisage — Tue, 29 May 2012 15:13:51 GMT

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

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

PGStats — Tue, 29 May 2012 15:52:15 GMT

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