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
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
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
You're welcome Mirisage, I acquired or refined all those tricks on this forum! - PG
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.