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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.