BookmarkSubscribeRSS Feed
thegraduate
Calcite | Level 5

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,

    7 REPLIES 7
    shivas
    Pyrite | Level 9

    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
    Calcite | Level 5


    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
    Opal | Level 21

    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
    Super User

    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
    PROC Star

    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
    Calcite | Level 5

    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
    PROC Star

    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.

    sas-innovate-2024.png

    Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

    Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

     

    Register now!

    What is Bayesian Analysis?

    Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

    Find more tutorials on the SAS Users YouTube channel.

    Click image to register for webinarClick image to register for webinar

    Classroom Training Available!

    Select SAS Training centers are offering in-person courses. View upcoming courses for:

    View all other training opportunities.

    Discussion stats
    • 7 replies
    • 1306 views
    • 3 likes
    • 5 in conversation