Help using Base SAS procedures

Array Help

Reply
Contributor
Posts: 24

Array Help

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;

customeraccount_type_201102account_type_201103account_type_201104
1TBAMultiSite
2SmallEnterpriseSmallEnterpriseMediumEnterprise
3TBATBATBA

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,

    Super Contributor
    Posts: 349

    Re: Array Help

    Posted in reply to thegraduate

    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

    Contributor
    Posts: 24

    Re: Array Help


    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,

    Respected Advisor
    Posts: 4,173

    Re: Array Help

    Posted in reply to thegraduate

    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=_Smiley Happy;
      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;

    Super User
    Posts: 10,044

    Re: Array Help

    Posted in reply to thegraduate

    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

    Super User
    Posts: 5,516

    Re: Array Help

    Posted in reply to thegraduate

    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.

    Contributor
    Posts: 24

    Re: Array Help

    Posted in reply to Astounding

    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,

    Super User
    Posts: 5,516

    Re: Array Help

    Posted in reply to thegraduate

    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.

    Ask a Question
    Discussion stats
    • 7 replies
    • 459 views
    • 3 likes
    • 5 in conversation