BookmarkSubscribeRSS Feed
deleted_user
Not applicable
I have month-end files which contain the rating history of borrowers. Supposedly, borrowers have rating history from their joined day to the day they quit.

I want to check whether a borrower with gaps in their ratings history. Anyone can share the SAS script?

Thanks!
11 REPLIES 11
Cynthia_sas
SAS Super FREQ
Hi:
Generally, when people ask for help writing a program it helps if you can provide a few sample (but fake) data records, like this:

[pre]
Data Set 1:
ID Name VarA VarB
1 Alan 111 222
2 Bob 111 333
3 Carl 444 222
4 Dave 555 777

Data Set 2:
ID Start End
1 01/01/07 02/01/07
2 03/15/06 05/15/08
3 01/15/06 03/14/07
4 02/14/07 05/29/08
[/pre]

And, then show a few records that illustrate the "after" -- and indicate whether you want a dataset for further processing or a report, and the kind of report (detail, summary, etc).

Also, SAS programs go through a compile phase and an execution phase, so technically, a SAS program is NOT a script. SAS is not like ASP or JSP or PHP or JavaScript.

You might consider doing some Google searches on SAS Basics or DATA step programs to locate some papers that outline how SAS works (as a programming language) and how SAS reads and writes data. Here are a few papers to get you started:
http://analytics.ncsu.edu/sesug/2002/TU06.pdf
http://www2.sas.com/proceedings/sugi29/252-29.pdf
http://www2.sas.com/proceedings/sugi30/250-30.pdf
http://analytics.ncsu.edu/sesug/2004/CC13-Larsen.pdf
http://analytics.ncsu.edu/sesug/2001/P-817.pdf

cynthia
deleted_user
Not applicable
The month-end files like this:

Jan 2008 Data file:

Timekey Borrid Rating_Model
31/01/2008 123456 ABC
31/01/2008 543210 XYZ
31/01/2008 987654 SAS


Feb 2008 Data file:

Timekey Borrid Rating_Model
29/02/2008 123456 ABC
29/02/2008 987654 SAS
29/02/2008 762153 ABC
29/02/2008 288624 XYZ

Mar 2008 Data file:

Timekey Borrid Rating_Model
31/03/2008 123456 ABC
31/03/2008 987654 SAS
31/03/2008 762153 ABC
31/03/2008 288624 XYZ
31/03/2008 543210 XYZ



I have around 40 month-end files and I will combine them and sort by borrid and timekey. I want to check whether a borrower with gaps in their ratings history.

The result I want to generate is like this:

Output file:

Timekey Borrid Rating_Model
31/03/2008 543210 XYZ
deleted_user
Not applicable
assuming a library ref CLAU holding these monthly files, named like BRATES0601 BRATES0602 .... BRATES0812 this code will extract tables:
1 Borrid, earliest_time, latest_time
2 Borrid, missing_time[pre]
libname user ( work clau ) ;
data t_range( keep= Borrid earliest_time latest_time )
t_missed( keep= Borrid missing_time ) ;

last_time = 0 ;
do until( last.borrid ) ;
set BRATES0601 BRATES0602 .... BRATES0812 ;
* until you SAS9.2 you need to list every input table on SET statement
in SAS9.2 you could use a dataset list like BRATES0: ;
by Borrid ; * and assuming in timeKey order ;
missing_time = intnx( 'month', last_time, 1, 'ending' ) ;
if first.borrid then earliest_time = time_key ;
else
do while( missing_time LT timeKey ) ;
output t_missed ;
missing_time = intnx( 'month', missing_time, 1, 'ending' ) ;
end ;
last_time = timeKey ;
end ;
latest_time = timeKey ;
output t_range ;
run ;[/pre]Then you have a complete list of missing TimeKeys for each borrower, except those which are missing at start or end of the whole range. I wasn't sure if your records were limited to borrowers "present" throughout the date range. If you need those late starters and early finishers, you can pick them out of the table T_RANGE checking earliest_time and latest_time against the dates you need.
Another table you might want to create would be the records or borrowers where the rating system changes over time.

Good Luck

PeterC
deleted_user
Not applicable
I have tried but cannot get the result (No error log was generated).

The result of T_Missed like this:

Borrid missing_time
123456 1256
123456 2546
123456 8541
123456 11025
.
.
.

The borrid is same for all records (538 records) and the missing_time is so large.

For the T_Range, there is no record generated.


PeterC, what do you think?
deleted_user
Not applicable
a typo-
So I was shamed into testing 😉 I used your example data.
When you ran the code, you may have seen a warning note like:[pre]NOTE: Variable time_key is uninitialized.[/pre]The relevant line was:[pre] if first.borrid then earliest_time = time_key ;[/pre] It should have referred to timeKey .
I hope these results were what you might have expected[pre]t_range 11:46 Tuesday, January 20, 2009

earliest_ latest_
Obs Borrid time time

1 123456 31/01/08 31/03/08
2 288624 29/02/08 31/03/08
3 543210 31/01/08 31/03/08
4 762153 29/02/08 31/03/08
5 987654 31/01/08 31/03/08
..................................................................... page break
t_missed 11:46 Tuesday, January 20, 2009

missing_
Obs Borrid time

1 543210 29/02/08[/pre]this code provided that print. Relevant to your comment that "the missing_time is so large", notice the format statements.[pre]option nocenter nonumber ls=64 ;
title 't_range' ;
proc print data= t_range ;
format latest_time earliest_time ddmmyy8.;
run;
title 't_missed' ;
proc print data= t_missed;
format missing_time ddmmyy8.;
run;[/pre] The formats are needed because new variables, created by assignment in a data step, do not inherit the format of the variables from which they are derived.

Hope it will work on your real data.

PeterC
deleted_user
Not applicable
Peter_C, Thank you so much!!!!!!

But there are still some problems, could you pls help again?

Problem:
1. The borrid is same for all records in the T_Missing;
2. The date in the print table is logically wrong. the data range is from 12/2004 to 08/2008, but some of the missing_time is, for example, 29/2/1960.
3. For the T_Range, there is still no record generated.

Let me show you my code (Combine is the data file combined all month-end files):

Proc sort data=Combine;
by timekey borrid;
run;

data t_range( keep= Borrid earliest_time latest_time )
t_missed( keep= Borrid missing_time ) ;
last_time = 0 ;
do until( last.borrid ) ;
set Combine;
missing_time = intnx( 'month', last_time, 1, 'ending' ) ;
if first.borrid then earliest_time = timekey ;
else do while( missing_time LT timeKey ) ;
output t_missed ;
missing_time = intnx( 'month', missing_time, 1, 'ending' ) ;
end ;
last_time = timeKey ;
end ;
latest_time = timeKey ;
output t_range ;
run ;


option nocenter nonumber ls=64 ;
title 't_range' ;
proc print data= t_range ;
format latest_time earliest_time ddmmyy10.;
run;

title 't_missed' ;
proc print data= t_missed;
format missing_time ddmmyy10.;
run;
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Your SASLOG should show an UNINITIALIZED message - you are missing a BY statement in the DATA step, after the SET, where you use the LAST.yourvarname logic.

Scott Barry
SBBWorks, Inc.
deleted_user
Not applicable
I ahve tried to use BY (please refer to my code), but there is an error msg and no result is generated:
"ERROR: BY variables are not properly sorted on data set WORK.COMBINE."


Code:


Proc sort data=combine;
by timekey;
run;

data t_range( keep= Borrid earliest_time latest_time )
t_missed( keep= Borrid missing_time ) ;
last_time = 0 ;
do until( last.borrid ) ;
set combine;
by borrid;
missing_time = intnx( 'month', last_time, 1, 'ending' ) ;
if first.borrid then earliest_time = timekey ;
else do while( missing_time LT timeKey ) ;
output t_missed ;
missing_time = intnx( 'month', missing_time, 1, 'ending' ) ;
end ;
last_time = timeKey ;
end ;
latest_time = timeKey ;
output t_range ;
run ;


option nocenter nonumber ls=64 ;
title 't_range' ;
proc print data= t_range ;
format latest_time earliest_time ddmmyy10.;
run;

title 't_missed' ;
proc print data= t_missed;
format missing_time ddmmyy10.;
run;
LinusH
Tourmaline | Level 20
In your case, your BY statement should (probably) look the same as in the preceding PROC SORT.

/Linus
Data never sleeps
deleted_user
Not applicable
the BY statements should be
in PROC SORT[pre] by BorrId timeKey ;[/pre]and in the DATA step[pre] by BorrId ;[/pre]
PeterC
deleted_user
Not applicable
I can get the answer, thanks for help!!!!!!!!!!!

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 11 replies
  • 847 views
  • 0 likes
  • 4 in conversation