BookmarkSubscribeRSS Feed
yelena
Fluorite | Level 6

Dear Sirs/Madams,

I have a dataset with default rates. I have IDs for different companies, time, exposures, etc. Over time the same company can default twice, say in 200703 and 200910. If the company defaults, I assign a default_flag=1. If not = 0. But I need to create such a dataset that if the same company defaults twice, I only assign default_flag=1 to the latest date, say 200910. How to do it?

 

This is the code that I wrote:

 

data data.downgrade1;

set data.default;

if BTM_Borrower_RTG >='083' & lag_RG <'083' & Exposure > 0 & lag_RG NE . then

default_flag=1;

run;

 

data data.downgrade2;

set data.downgrade1;

by Instrument_ID;

if last.Instrument_ID;

where default_flag=1;

run;

 

But in my code I can only see the companies with default_flag=1 in downgrade2 but I also want to see all other companies. How can I do it?

 

Thank you for your help.

10 REPLIES 10
Jagadishkatam
Amethyst | Level 16

Please try this untested code.

 

First we need to sort the defaults datasets by company and date. Then try the below code

 

 

data data.downgrade1;
set data.default;
by company date;
if first.company=0 and last.company=1 then default_flag=1;
run;

 

 

Thanks,
Jag
yelena
Fluorite | Level 6

Thank you, Jag, for your reply. But if I also need to create something like this. I need to get the output highlihted in yellow. Let me explain how it works. 

 

Assume we have companies. Some default (say, their ratings is 8 and then they never get better). So we set the default date when their ratings drop to 8 and then we don't care about any other observations (replace 0 with .). But if some other companies after default get better (say their ratings was 8 and then jumped up to 2 and then after a while back to 9 again) we want to report the latest default date 9 and set the dummy equal to 1 which corresponds to that date. 

 

How will then my code look like if I currently have code that produces code in column D but I want to make column in E. 

yelena
Fluorite | Level 6

Dear Jag,

It seems that I have some problem with this code:

ERROR: BY variables are not properly sorted on data set DATA.DOWNGRADE1.

 

yelena
Fluorite | Level 6

Dear Jag,

I tried to run the code but it does not produce what I tried to do. But it ran eventually when I sorted the variables. Let me try to explain again. In my dataset I assign default_flag=1 to the companies the have ratings higher than 8-3. If the company has a lower rating, then default_flag=0. But if the company defaulted (in 200703) but then got better but then defaulted again (200909) then I'd report only one default flag for 200909. So I want to report the latest default date when the default flag =1. How to do it? Thank you very much for your help.

 

 

 

yelena
Fluorite | Level 6

Dear Sirs/Madams, 

 

Assume we have companies. Some default (say, their ratings is 8 and then they never get better). So we set the default date when their ratings drop to 8 and then we don't care about any other observations (replace 0 with .). But if some other companies after default get better (say their ratings was 8 and then jumped up to 2 and then after a while back to 9 again) we want to report the latest default date 9 and set the dummy equal to 1 which corresponds to that date. 

 

How will then my code look like if I currently have code that produces code in column D but I want to make column in E. 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

To get some good answers, post some test data, in the form of a datastep - the below topic will help - and what the output should look like.  I am afraid I am not downloading any Excel files.

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

Ksharp
Super User
data have;
infile cards expandtabs truncover;
input TIME_ID	ID	RTG	Default;
cards;
200501	1	1	.
200502	1	2	.
200503	1	8	1
200504	1	9	.
200501	2	1	.
200502	2	8	.
200503	2	2	.
200504	2	9	1
;
run;
data want; 
 set have;
 by id;
 retain found 0;
 if first.id then found=0;
 if not found then want=0;
 if Default=1 then do;
  found=1;
  want=1;
 end;
drop found;
run;
yelena
Fluorite | Level 6

Dear Xia,

Your code is excellent. Thank you very much for your help. I tried to run it and it works but I have a very little nuance which I probably did not emphasize. assume I have one company that defaulted twice, in 200703 and 200910, so the default_flag=1 in both cases. Can we somehow have all 0 for 'want' variable priot to the latest default date (200910) and nothing after (.). So that for each company the variable 'want' will have zero up until 200909, one for 200910 and . after 200910. How will this code be modified?

 

 

yelena
Fluorite | Level 6
data have;
infile cards expandtabs truncover;
input TIME_ID ID RTG Default;
cards;
200501 1 1 .
200502 1 2 .
200503 1 8 1
200504 1 9 .
200501 2 1 .
200502 2 8 1
200503 2 2 .
200504 2 9 1
;
ballardw
Super User

Here's one way, find the desired date and merge back

data have;
infile cards expandtabs truncover;
input TIME_ID	ID	RTG	Default;
cards;
200501	1	1	.
200502	1	2	.
200503	1	8	1
200504	1	9	.
200501	2	1	.
200502	2	8	1
200503	2	2	.
200504	2	9	1
;
run;

proc summary data=have nway;
   where default=1;
   class id;
   var time_id;
   output out=lastdef (drop=_:) max=;
run;

data want;
   merge
      have (drop=default)
      lastdef (in=indef)
   ;
   by id time_id;
   if indef then default=1;
run;

The HAVE set needs to be sorted by id time_id if it actually isn't already.

 

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 10 replies
  • 1730 views
  • 0 likes
  • 5 in conversation