topic Re: Array Help in SAS Procedures

Array Help

thegraduate — Wed, 18 Apr 2012 08:26:23 GMT

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,

Re: Array Help

shivas — Wed, 18 Apr 2012 08:48:50 GMT

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

Re: Array Help

thegraduate — Wed, 18 Apr 2012 09:09:54 GMT

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,

Re: Array Help

Patrick — Wed, 18 Apr 2012 10:31:42 GMT

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;

Re: Array Help

Ksharp — Thu, 19 Apr 2012 02:56:11 GMT

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

Re: Array Help

Astounding — Thu, 19 Apr 2012 04:07:36 GMT

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.

Re: Array Help

thegraduate — Thu, 19 Apr 2012 11:20:56 GMT

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,

Re: Array Help

Astounding — Thu, 19 Apr 2012 12:38:49 GMT

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.