Help using Base SAS procedures

Multiple counters in datastep

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Multiple counters in datastep

CCorrected the question and reposted in the correct forum....

how can I accomplish this.Thanks in advance

family.  Member. Category. Amount

A.         1.            Bio.          100

A.          1.           Tab.            50

A.           2.           Che.           75

A.             2.         Bio.           100

A.            2.          Lei.              50

A.             3.          Bio.           150

A.             3.          Pas.            200

B. Etc etc

i have the above condition.my goal is to check for the following conditions and keep those records and the unqualified records into another dataset

1.want to keep a max of 2 records for Bio, Che,tab categories but, can keep upto 3 records for Pas, upto 4 for lei.

2.the total should not exceed 500/ family

3. The last category should get partial credit to equal 500/family

desired output:

family.  Member.  Category.  Amount

A.         1.              Bio.          100

A.          1.             Tab.          50

A.           2           Che.            75

A.            2.            Bio.          100

A.            2.             Lei.            50

A.            3.            Pas.           125

lost bio for member 3 as bio count > 2

the last category got partial credit 125 instead of 200 (500-(100+50+75+100+50)  to make it 500/ family.


Accepted Solutions
Solution
‎01-20-2015 10:05 AM
Super User
Posts: 10,023

Re: Multiple counters in datastep

If I understand you correctly .

data have;
input family $ Member $ Category $ Amount ;
cards;
A.         1.            Bio          100
A.          1.           Tab            50
A.           2.           Che           75
A.             2.         Bio           100
A.            2.          Lei              50
A.             3.          Bio          150
A.             3.          Pas            200
;
run;
data want not_want;
 if _n_ eq 1 then do;
  if 0 then set have;
  declare hash h();
  h.definekey('Category');
  h.definedata('n');
  h.definedone();
 end;
set have;
by family;
retain total;
if h.find()=0 then do;n+1;h.replace();end;
 else do;n=1;h.replace();end;
if first.family then total=500;
if Category='Bio' and n lt 3 then do;
  if total gt 0 and amount gt total then amount=total;
  if total gt 0 then output want;
    else output not_want;
  total+(-1*amount);
end;
 else if Category='Che' and n lt 3 then do;
         if total gt 0 and amount gt total then amount=total;
         if total gt 0 then output want;
           else output not_want;
         total+(-1*amount);
      end;
   else if Category='Tab' and n lt 3 then do;
          if total gt 0 and amount gt total then amount=total;
          if total gt 0 then output want;
           else output not_want;
          total+(-1*amount);
        end; 
    else if Category='Pas' and n lt 4 then do;
           if total gt 0 and amount gt total then amount=total;
           if total gt 0 then output want;
            else output not_want;
           total+(-1*amount);
          end;
       else if Category='Lei' and n lt 5 then do;
            if total gt 0 and amount gt total then amount=total;
            if total gt 0 then output want;
             else output not_want;
            total+(-1*amount);
            end;
        else output not_want;
drop total n;
run;
   
 

Xia Keshan

View solution in original post


All Replies
Super User
Super User
Posts: 7,942

Re: Multiple counters in datastep

Well, part 1 is quite simple:

data want other;

     set have;

     if category in ("Bio","Tab","Che") and member <= 2 then output want;

     else if category="Pas" and member <= 3 then output want;

     else if category="Lei" and member <= 4 then output want;

     else output other;

run;

As for your other two points, I am afraid I really don't understand you.  If you have the formula worked out however, just do a proc sort to get the data in order, then set up some counter variables using a by group and retain statements:

data want;

     set want;

     by family;

     retain xyz;

     if first.family then xyz=0;

     else xyz=xyz+1;

     if last.familiy then...;

run;

Occasional Contributor
Posts: 5

Re: Multiple counters in datastep

Part1- want to set up counters and if counter le 2 for bio, tab, Che OR counter le3 for pas OR

Le 4 for che...I want to keep the records.

2nd step: counter for amounts.

If accumulator amount le  500/ family keep the record.

3rd step: check to see if the family total is lt 500 and the next record has amount which totals the accumulator to get 500 then back out the amount to make it 500 max.

In the above example records up to the last but 1 record is lt 2000 but the family had more records so that next record gets 125 only(the remaining amount to make it 500) to make the family total 500.

Hope iam clear this time

Trusted Advisor
Posts: 1,301

Re: Multiple counters in datastep

data foo;

input family $ member category $ amount;

cards;

A 1 Bio 100

A 1 Tab 50

A 2 Che 75

A 2 Bio 100

A 2 Lei 50

A 3 Bio 150

A 3 Pas 200

;

run;

proc format;

invalue cat2idx

'Bio' = 1

'Tab' = 2

'Che' = 3

'Lei' = 4

'Pas' = 5;

run;

data v/view=v;

array cnt[6] _temporary_;

array max[5] _temporary_ (3*2 4 3);

do until(last.family);

set foo;

by family member;

idx=input(category, cat2idx.);

if cnt[idx]>=max[idx] then continue;

cnt[idx]+1;

output;

end;

call missing(of cnt

  • );
  • drop idx;

    run;

    data bar;

    _n_=0;

    do while(1);

    set v;

    by family member;

    if last.family then leave;

    _n_+amount;

    output;

    end;

    amount=500-_n_;

    output;

    run;

    Message was edited by: FriedEgg Corrected issue with original posting.

    Frequent Contributor
    Posts: 137

    Re: Multiple counters in datastep

    Sir, if you don't mind and if you have a few mins, can you please pen down a line or two to explain how  the do while(1);/*construct*/ works?

    so, do while what is true here? I am not seeing any condtion here? does 1 refer to something?if something is true, what does it check for false?

    I'd really appreciate it. Please and thanks

    Regards,

    Charlotte

    Trusted Advisor
    Posts: 1,301

    Re: Multiple counters in datastep

    Posted in reply to CharlotteCain

    ,

    This is an infinite loop.  It will always evaluate to True (1=True).  Instead of looping until I encounter a False condition I instead wanted to break my loop with the LEAVE statement.

    Super User
    Super User
    Posts: 7,942

    Re: Multiple counters in datastep

    Isn't that a tad risqué?  Infinite loop and all.  Just to check, could you not do:

    do until (last.family);

    Avoiding the leave statement.

    Trusted Advisor
    Posts: 1,301

    Re: Multiple counters in datastep

    ,

    I would not call it risqué, it is a very common construct.  The reason I do not is because I find it more clear this way, as I never expect that condition to ever actually occur the way the code within the loop is written.

    If I were going to use a do until, I would rewrite the code inside the loop as follows.

    do until(last.family);

    set v;

    by family member;

    if not last.family then do;

    _n_+amount;

    output;

    end;

    end;

    Occasional Contributor
    Posts: 5

    Re: Multiple counters in datastep

    THank you fried egg..Sad only one can be coded as correct answer.but sure I learnt different ways to tackle this problem.

    APpreciate all the help and answers.Didnt have time to play with the rest of the answers.

    Solution
    ‎01-20-2015 10:05 AM
    Super User
    Posts: 10,023

    Re: Multiple counters in datastep

    If I understand you correctly .

    data have;
    input family $ Member $ Category $ Amount ;
    cards;
    A.         1.            Bio          100
    A.          1.           Tab            50
    A.           2.           Che           75
    A.             2.         Bio           100
    A.            2.          Lei              50
    A.             3.          Bio          150
    A.             3.          Pas            200
    ;
    run;
    data want not_want;
     if _n_ eq 1 then do;
      if 0 then set have;
      declare hash h();
      h.definekey('Category');
      h.definedata('n');
      h.definedone();
     end;
    set have;
    by family;
    retain total;
    if h.find()=0 then do;n+1;h.replace();end;
     else do;n=1;h.replace();end;
    if first.family then total=500;
    if Category='Bio' and n lt 3 then do;
      if total gt 0 and amount gt total then amount=total;
      if total gt 0 then output want;
        else output not_want;
      total+(-1*amount);
    end;
     else if Category='Che' and n lt 3 then do;
             if total gt 0 and amount gt total then amount=total;
             if total gt 0 then output want;
               else output not_want;
             total+(-1*amount);
          end;
       else if Category='Tab' and n lt 3 then do;
              if total gt 0 and amount gt total then amount=total;
              if total gt 0 then output want;
               else output not_want;
              total+(-1*amount);
            end; 
        else if Category='Pas' and n lt 4 then do;
               if total gt 0 and amount gt total then amount=total;
               if total gt 0 then output want;
                else output not_want;
               total+(-1*amount);
              end;
           else if Category='Lei' and n lt 5 then do;
                if total gt 0 and amount gt total then amount=total;
                if total gt 0 then output want;
                 else output not_want;
                total+(-1*amount);
                end;
            else output not_want;
    drop total n;
    run;
       
     
    
    

    Xia Keshan

    Occasional Contributor
    Posts: 5

    Re: Multiple counters in datastep

    Thank you all.. iam trying both the codes. . Xian keshan, this code is working perfectly for one family.. when I added a family B then family A iis working well but, the next family which is B is outputting only the categories that have not maxed in the previous category. . In other words,  the condition is working globally for all families.how to fix it?

    Trusted Advisor
    Posts: 1,301

    Re: Multiple counters in datastep

    Try changing the following line:

      h.definekey('Category');

    to

      h.definekey('family','Category');

    OR

    add "h.clear()" to the end of the data step (before the run and after the last else), this would be probably be better that the above.

    Message was edited by: FriedEgg

    Super User
    Posts: 10,023

    Re: Multiple counters in datastep

    Yeah, Matt is right . I didn't test multi-family situation.

    data have;
    input family $ Member $ Category $ Amount ;
    cards;
    A.         1.            Bio          100
    A.          1.           Tab            50
    A.           2.           Che           75
    A.             2.         Bio           100
    A.            2.          Lei              50
    A.             3.          Bio          150
    A.             3.          Pas            200
    B.         1.            Bio          100
    B.          1.           Tab            50
    B.           2.           Che           75
    B.             2.         Bio           100
    B.            2.          Lei              50
    B.             3.          Bio          150
    B.             3.          Pas            200
    ;
    run;
    data want not_want;
     if _n_ eq 1 then do;
      if 0 then set have;
      declare hash h();
      h.definekey('family','Category');
      h.definedata('n');
      h.definedone();
     end;
    set have;
    by family;
    retain total;
    if h.find()=0 then do;n+1;h.replace();end;
     else do;n=1;h.replace();end;
    if first.family then total=500;
    if Category='Bio' and n lt 3 then do;
      if total gt 0 and amount gt total then amount=total;
      if total gt 0 then output want;
        else output not_want;
      total+(-1*amount);
    end;
     else if Category='Che' and n lt 3 then do;
             if total gt 0 and amount gt total then amount=total;
             if total gt 0 then output want;
               else output not_want;
             total+(-1*amount);
          end;
       else if Category='Tab' and n lt 3 then do;
              if total gt 0 and amount gt total then amount=total;
              if total gt 0 then output want;
               else output not_want;
              total+(-1*amount);
            end; 
        else if Category='Pas' and n lt 4 then do;
               if total gt 0 and amount gt total then amount=total;
               if total gt 0 then output want;
                else output not_want;
               total+(-1*amount);
              end;
           else if Category='Lei' and n lt 5 then do;
                if total gt 0 and amount gt total then amount=total;
                if total gt 0 then output want;
                 else output not_want;
                total+(-1*amount);
                end;
            else output not_want;
    drop total n;
    run;
       
     
    
    

    Xia Keshan

    Respected Advisor
    Posts: 3,156

    Re: Multiple counters in datastep

    Another alternative:

    data have;

         input family $ Member $ Category $ Amount;

         cards;

    A.         1.            Bio          100

    A.          1.           Tab            50

    A.           2.           Che           75

    A.             2.         Bio           100

    A.            2.          Lei              50

    A.             3.          Bio          150

    A.             3.          Pas            100

    A.             3.          Pas            75

    B.         1.            Bio          100

    B.          1.           Tab            50

    B.          1.           Tab            50

    B.          1.           Tab            50

    B.             3.          Pas            100

    B.             3.          Pas            10

    B.             3.          Pas            10

    B.             3.          Pas            10

    B.             3.          Pas            10

    B.          1.           Tab            50

    B.           2.           Che           75

    B.             2.         Bio           100

    B.            2.          Lei              50

    B.             3.          Bio          150

    B.             3.          Pas            200

    ;

    run;

    data want_1;

         array c(6) _temporary_;

         array ca(5) $ 3  _temporary_ ('Bio' 'Tab' 'Che' 'Pas' 'Lei');

         set have;

         by family notsorted;

         if first.family then

               call missing (of c(*));

         do i=1 to dim(ca);

               if Category=ca(i) then

                    do;

                         c(i)+1;

                         if c(6)<500 then

                               do;

                                    if i<=3 then

                                         do;

                                               if c(i)<=2 then

                                                    c(6)+amount;

                                               else delete;

                                         end;

                                    else if i=4 then

                                         do;

                                               if c(i)<=3 then

                                                    c(6)+amount;

                                               else delete;

                                         end;

                                    else if i=5 then

                                         do;

                                               if c(i)<=4 then

                                                    c(6)+amount;

                                               else delete;

                                         end;

                               end;

                         else delete;

                         leave;

                    end;

         end;

         if c(6)>500 then

               amount=amount-(c(6)-500);

         drop i;

    run;


    Contributor
    Posts: 52

    Re: Multiple counters in datastep

    A solution.

    data want(keep=family member category amount);
    array aCnt(5) $ ('Bio','Che','Tab','Pas','Lei');
    array bCnt(5) (2,2,2,3,4);
    array cCnt(5) ;
    length zAmt zStop 8.;
    zMax=500;

    set have;
    by family;

    if first.family then do; zAmt=0; zStop=0; call missing(of cCnt(*)); end;
        do i = 1 to dim(aCnt);
           if Category = aCnt(i) then do;
              cCnt(i)+1;
              if cCnt(i) <= bCnt(i) then do;
                 zAmt+Amount;
                 if zAmt < zMax then output;
                 else do;
                    if zStop=0 then do;
                       zStop=1;
                       Amount = zMax -(zAmt-Amount);
                       output;
                    end;
                 end;
              end;
           end;
        end;
    run;

    🔒 This topic is solved and locked.

    Need further help from the community? Please ask a new question.

    Discussion stats
    • 17 replies
    • 617 views
    • 3 likes
    • 7 in conversation