topic Re: Find a vlaue in an array and subsequent variable in SAS Programming

Find a vlaue in an array and subsequent variable

podarum — Thu, 01 Dec 2016 21:10:00 GMT

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

Re: Find a vlaue in an array and subsequent variable

Reeza — Thu, 01 Dec 2016 21:14:30 GMT

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

Re: Find a vlaue in an array and subsequent variable

Astounding — Thu, 01 Dec 2016 22:12:54 GMT

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;

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.

Re: Find a vlaue in an array and subsequent variable

podarum — Fri, 02 Dec 2016 16:20:01 GMT

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;

drop i;

run;