Array Help

[thegraduate]

All,

I have created the following array to identify the first valid segment value for a customers, here is a data example of what it does;

customer	account_type_201102	account_type_201103	account_type_201104
1		TBA	MultiSite
2	SmallEnterprise	SmallEnterprise	MediumEnterprise
3	TBA	TBA	TBA

data folder.test(compress=yes);

set work.BadFlags;

format account_type_acq $20.;

     i=1;

     array segmentarray

account_type_: ;

do while (account_type_acq='');

account_type_acq=segmentarray;

i+1;

end;

run;

So a variable is created (account_type_acq) to identify the first allocated segment. So in the case above, customer 1 is "TBA", 2 is "Small Enterprise" etc. The array will check every column until it hits the end, there is no array range as you can see, this is because every month, a file is created to indentify the customer segment. So for new customers that were acquired earlier, the newest file will have the segment.

My question is, I want the array to skip "TBA" also and only select a value that is "SmallEnterprise"/"MediumEnterprise"/"Multisite". Currently it skips the blank values but I also want it to skip "TBA". I've tried adding an OR statement to the do while but I receive an error message "Subscript out of range".

any help will be appreciated.

Thanks,

[shivas]

Hi,

Try this..Hope its helps...please post the output you want.

data one;

input customer account_type_201102 $ 5-20 account_type_201103 $ 22-37 account_type_201104 $ 41-57;

cards;

1                 TBA            MultiSite

2 SmallEnterprise SmallEnterprise MediumEnterprise

3 TBA             TBA            TBA

;

run;

data tst;

set work.one;

format account_type_acq $20.;

     i=1;

     array segmentarray

account_type_: ;

do i=1 to dim(segmentarray);

if segmentarray ='' or segmentarray='TBA' then

account_type_acq='';

else account_type_acq=segmentarray;

end;

run;

Thanks,

Shiva

[thegraduate]

Shiva,

That didn't work as if it detects "TBA" at all in any of the months, it blanks out the value.;

Customer     Account_Type_201101          Account_Type_201102         Account_Type_201103

1                  SE                                        ME                                   TBA

2                   TBA                                      TBA                                  TBA

Your code blanks out the segment type for the example above.  What I want it to do is to obtain the first valid segment "SE"/"ME"/"MP". But I always want to populate the value, so customer 2 has TBA all the way through, then account_type is TBA.

Does this makes sense?

Many thanks,

[Patrick]

Below code should do what you're after:

data have;
infile datalines dsd truncover;
input customer account_type_201102:$20. account_type_201103:$20. account_type_201104:$20.;
datalines;
1,,TBA,MultiSite
2,SmallEnterprise,SmallEnterprise,MediumEnterprise
3,TBA,TBA,TBA
;
run;

proc format;
  value $NoTBA (min=20)
    'TBA'=' '
  ;
run;

data want(drop=_:);
  set have;
  format account_type_acq $20.;
  array segmentarray

account_type_: ;
  do _i=1 to dim(segmentarray);
    if put(segmentarray(_i),$NoTBA.) ne '' then
      do;
        account_type_acq=put(segmentarray(_i),$NoTBA.);
        leave;
      end;
  end;
  if account_type_acq='' then account_type_acq=coalescec(of segmentarray(*));
run;

proc print data=want;
run;

[Ksharp]

I prefer to Hash Table for your this special situation.

data have;
infile datalines dsd truncover;
input customer account_type_201102:$20. account_type_201103:$20. account_type_201104:$20.;
datalines;
1,,TBA,MultiSite
2,SmallEnterprise,SmallEnterprise,MediumEnterprise
3,TBA,TBA,TBA
;
run;
data _null_;
if _n_ eq 1 then do;
length type $ 20;
 declare hash ha();
  ha.definekey('type');
  ha.definedone();
end;
 set have;
 array segmentarray{*} account_type_: ;
 do i=1 to dim(segmentarray);
  if ha.check(key:segmentarray{i}) ne 0 and not missing(segmentarray{i}) then do; 
   put 'Found A New Value:' segmentarray{i} ;
   type=segmentarray{i};ha.add();
   leave;
   end;
  end;
run;

Ksharp

[Astounding]

I'd just change the DO loop to:

do until (account_type_acq not in (' ', 'TBA'));

You will run the risk of an array subscript out of range, at least in theory.  If a CUSTOMER has all blanks and TBAs, and there is no valid account type to be found, you'll get that error.

Good luck.

[thegraduate]

Guys,

Thanks for the replies, i did find this solution to work;

format account_type_acq $20.;

i=1;

array segmentarray

account_type_:;

do i=1 to dim(segmentarray) until (account_type_acq IN('SE','ME','MU'));

           account_type_acq=segmentarray;

i+1;

end;

run;

This is similiar to what Astounding mentioned but the reverse.

Thanks you all for your input.

Regards,

[Astounding]

This is a good idea to combine methods to end the loop.  But ...

Your DO loop will increment i at the bottom of the loop.  You should remove this statement:

i + 1;

Otherwise you will be examining just the odd array elements and skipping the even ones.