Hi, I'm going to try to explain this as best as I can.
I have a dataset with values Status11 to Status1 (11 vars) and another 11 vars called Bal11 to Bal1.. among other useful information (eg. acct.no, province, etc.)
I need to find when an account went past due (eg. PD1) form the array and the subsequen Balance of that same period.
HAVE:
Acct Othervars Status11 Status10 .......... Status2 Status1 Bal11 Bal10 ......... Bal2 Bal1
001 bla PD0 PD1 PD2 PD3 500 450 450 420
002 bla PD0 PD0 PD1 PD0 300 200 200 200
003 bla PD0 PD0 PD1 PD1 560 500 340 300
004 bla PD0 PD0 PD0 PD0 230 220 200 190
I need when an account first went to PD1 to create a new field call it Bad30 = 1 and Bal30 = whatever the balance is .. and same for BAD60 (PD2) and BAD90 (PD3)...
WANT:
Acct Othervars Status11 Status10 .......... Status2 Status1 Bal11 Bal10 ......... Bal2 Bal1 BAD30 BAL30
001 bla PD0 PD1 PD2 PD3 500 450 450 420 1 450
002 bla PD0 PD0 PD1 PD0 300 200 200 200 1 200
003 bla PD0 PD0 PD1 PD1 560 500 340 300 1 340
004 bla PD0 PD0 PD0 PD0 230 220 200 190 0 0
Thanks
Here's one way to look at it:
data want;
set have;
array st {11} status11-status1;
array ba {11} bal11-bal1;
Bad30=0;
Bad60=0;
Bad90=0;
do i=1 to 11;
if status{i} = 'PD1' and Bad30=0 then do;
Bad30=1;
Bal30=ba{i};
end;
else if status{i}='PD2' and bad60=0 then do;
bad60=1;
Bal60=ba{i};
end;
else if status{i}='PD3' and bad90=0 then do;
Bad90=1;
Bal90=ba{i};
end;
end;
drop i;
run;
There are other results you could attempt. For example, instead of using 0 or 1 for Bad30, you could use 0 or a digit 1 through 11 to indicate which time period is the first one with the problem.
Here's one way to look at it:
data want;
set have;
array st {11} status11-status1;
array ba {11} bal11-bal1;
Bad30=0;
Bad60=0;
Bad90=0;
do i=1 to 11;
if status{i} = 'PD1' and Bad30=0 then do;
Bad30=1;
Bal30=ba{i};
end;
else if status{i}='PD2' and bad60=0 then do;
bad60=1;
Bal60=ba{i};
end;
else if status{i}='PD3' and bad90=0 then do;
Bad90=1;
Bal90=ba{i};
end;
end;
drop i;
run;
There are other results you could attempt. For example, instead of using 0 or 1 for Bad30, you could use 0 or a digit 1 through 11 to indicate which time period is the first one with the problem.
This is perfect ..thanks so much...
Some minor fixes if anyone wants to reference this code:
data want;
set have;
array st {11} status11-status1;
array ba {11} bal11-bal1;
Bad30=0;
Bad60=0;
Bad90=0;
do i=1 to 11;
if st{i} = 'PD1' and Bad30=0 then do;
Bad30=1;
Bal30=ba{i};
end;
else if st{i}='PD2' and bad60=0 then do;
bad60=1;
Bal60=ba{i};
end;
else if st{i}='PD3' and bad90=0 then do;
Bad90=1;
Bal90=ba{i};
end;
end;
drop i;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.