BookmarkSubscribeRSS Feed
Mirisage
Obsidian | Level 7

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

3 REPLIES 3
PGStats
Opal | Level 21

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
Mirisage
Obsidian | Level 7

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
Opal | Level 21

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

PG

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 727 views
  • 0 likes
  • 2 in conversation