topic Re: Urgent: About SAS scripts in SAS Enterprise Guide

Urgent: About SAS scripts

deleted_user — Tue, 20 Jan 2009 03:02:16 GMT

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!

Re: Urgent: About SAS scripts

Cynthia_sas — Tue, 20 Jan 2009 04:44:56 GMT

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

Re: Urgent: About SAS scripts

deleted_user — Tue, 20 Jan 2009 06:13:26 GMT

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

Re: Urgent: About SAS scripts

deleted_user — Tue, 20 Jan 2009 10:38:18 GMT

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

Re: Urgent: About SAS scripts

deleted_user — Wed, 21 Jan 2009 01:59:26 GMT

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?

Re: Urgent: About SAS scripts

deleted_user — Wed, 21 Jan 2009 10:44:57 GMT

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

Re: Urgent: About SAS scripts

deleted_user — Thu, 22 Jan 2009 01:42:04 GMT

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;

Re: Urgent: About SAS scripts

sbb — Thu, 22 Jan 2009 04:30:12 GMT

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.

Re: Urgent: About SAS scripts

deleted_user — Thu, 22 Jan 2009 06:19:49 GMT

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;

Re: Urgent: About SAS scripts

LinusH — Thu, 22 Jan 2009 07:07:36 GMT

In your case, your BY statement should (probably) look the same as in the preceding PROC SORT.

/Linus

Re: Urgent: About SAS scripts

deleted_user — Thu, 22 Jan 2009 08:56:18 GMT

the BY statements should be
in PROC SORT[pre] by BorrId timeKey ;[/pre]and in the DATA step[pre] by BorrId ;[/pre]
PeterC

Re: Urgent: About SAS scripts

deleted_user — Thu, 22 Jan 2009 09:55:38 GMT

I can get the answer, thanks for help!!!!!!!!!!!