BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Kksasbio
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

17 REPLIES 17
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Kksasbio
Calcite | Level 5

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

FriedEgg
SAS Employee

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.

    CharlotteCain
    Quartz | Level 8

    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

    FriedEgg
    SAS Employee

    ,

    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.

    RW9
    Diamond | Level 26 RW9
    Diamond | Level 26

    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.

    FriedEgg
    SAS Employee

    ,

    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;

    Kksasbio
    Calcite | Level 5

    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.

    Ksharp
    Super User

    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

    Kksasbio
    Calcite | Level 5

    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?

    FriedEgg
    SAS Employee

    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

    Ksharp
    Super User

    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

    Haikuo
    Onyx | Level 15

    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;


    billfish
    Quartz | Level 8

    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;

    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
    • 17 replies
    • 1596 views
    • 3 likes
    • 7 in conversation