Find a vlaue in an array and subsequent variable

[podarum]

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

[Reeza]

1. Create an array for each of your sets of variables.
2. Create an array for your output variables.
3. Use the WHICHC function to find the string of interest - it finds the first instance.
4. Use the index from 2 to pull information from other arrays as necessary.

[Astounding · Accepted solution]

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.

[podarum]

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;