topic Re: Multiple counters in datastep in SAS Procedures

Multiple counters in datastep

Kksasbio — Tue, 20 Jan 2015 10:33:05 GMT

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.

Re: Multiple counters in datastep

RW9 — Tue, 20 Jan 2015 10:39:05 GMT

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;

Re: Multiple counters in datastep

Kksasbio — Tue, 20 Jan 2015 14:06:42 GMT

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

Re: Multiple counters in datastep

FriedEgg — Tue, 20 Jan 2015 14:29:59 GMT

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.

Re: Multiple counters in datastep

CharlotteCain — Tue, 20 Jan 2015 15:03:15 GMT

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

Re: Multiple counters in datastep

Ksharp — Tue, 20 Jan 2015 15:05:21 GMT

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

Re: Multiple counters in datastep

FriedEgg — Tue, 20 Jan 2015 15:14:32 GMT

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.

Re: Multiple counters in datastep

RW9 — Tue, 20 Jan 2015 15:24:52 GMT

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.

Re: Multiple counters in datastep

FriedEgg — Tue, 20 Jan 2015 15:47:31 GMT

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;

Re: Multiple counters in datastep

Kksasbio — Tue, 20 Jan 2015 18:16:32 GMT

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?

Re: Multiple counters in datastep

FriedEgg — Tue, 20 Jan 2015 18:33:46 GMT

Try changing the following line:

h.definekey('Category');

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

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

Re: Multiple counters in datastep

Ksharp — Wed, 21 Jan 2015 04:30:50 GMT

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

Re: Multiple counters in datastep

Haikuo — Fri, 23 Jan 2015 18:36:56 GMT

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. 3. Pas 100

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;

else delete;

leave;

end;

if c(6)>500 then

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

drop i;

run;

Re: Multiple counters in datastep

billfish — Fri, 23 Jan 2015 21:11:19 GMT

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;

Re: Multiple counters in datastep

Haikuo — Fri, 23 Jan 2015 21:24:11 GMT

Your code needs further tweak. Your output stops before hitting 500.

Re: Multiple counters in datastep

billfish — Sat, 24 Jan 2015 00:45:07 GMT

No tweeking needed as far as total sum.
This is the output for table have:

Obs   family   Member   Category   Amount
1    A.   1   Bio   100
2    A.   1   Tab   50
3    A.   2   Che   75
4    A.   2   Bio   100
5    A.   2   Lei   50
6    A.   3   Pas   125 - total sum -> 500
7    B.   1   Bio   100
8    B.   1   Tab   50
9    B.   2   Che   75
10   B.   2   Bio   100
11   B.   2   Lei   50
12   B.   3   Pas   125 - total sum-> 500

You may have been looking at the code line:

if zAmt < zMax then output;

and thought that the output stops at < 500. Suppose zAmt (before truncating amount) is exactly 500.

Then that particular row will be outputted with the variable zStop becoming = 1. So the next record, even if amount = 1, will not be outputted.

I assume that amount > 0 for all records.

My point is to stop the outputting once the total sum >= 500.

Now, if some records have amount=0 and to be outputted after total sum >= 500, then one goes from
if zAmt < zMax then output;
to
if ((zAmt < zMax) and (zStop=0)) or (amount=0) then output;

and instead of:
zMax=500;
zStop=0;

one changes to:
zMax=500;
retain zStop;

In fact the last change needs to be made for any case. I did not see the error until I added another record for family A in the input.

Re: Multiple counters in datastep

Haikuo — Sat, 24 Jan 2015 00:57:48 GMT

Hmmm. Not sure what is happening, when I copy-paste your code and use the data in 's post, I got this log and output:

23 data have;

24 input family $ Member $ Category $ Amount ;

25 cards;

NOTE: The data set WORK.HAVE has 14 observations and 4 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

40 ;

41 run;

43 data want(keep=family member category amount);

44 array aCnt(5) $ ('Bio','Che','Tab','Pas','Lei');

45 array bCnt(5) (2,2,2,3,4);

46 array cCnt(5) ;

47 length zAmt zStop 8.;

48 zMax=500;

49 set have;

50 by family;

52 if first.family then do; zAmt=0; zStop=0; call missing(of cCnt(*)); end;

53 do i = 1 to dim(aCnt);

54 if Category = aCnt(i) then do;

55 cCnt(i)+1;

56 if cCnt(i) <= bCnt(i) then do;

57 zAmt+Amount;

58 if zAmt < zMax then output;

59 else do;

60 if zStop=0 then do;

61 zStop=1;

62 Amount = zMax -(zAmt-Amount);

63 output;

64 end;

2 The SAS System 18:49 Friday, January 23, 2015

65 end;

66 end;

67 end;

68 end;

69 run;

NOTE: There were 14 observations read from the data set WORK.HAVE.

NOTE: The data set WORK.WANT has 10 observations and 4 variables.

NOTE: DATA statement used (Total process time):

real time 0.16 seconds

cpu time 0.01 seconds

71 proc print;run;

NOTE: There were 10 observations read from the data set WORK.WANT.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.63 seconds

cpu time 0.00 seconds

1	A.	1.	Bio	100
2	A.	1.	Tab	50
3	A.	2.	Che	75
4	A.	2.	Bio	100
5	A.	2.	Lei	50
6	B.	1.	Bio	100
7	B.	1.	Tab	50
8	B.	2.	Che	75
9	B.	2.	Bio	100
10	B.	2.	Lei	50

Re: Multiple counters in datastep

Kksasbio — Thu, 12 Feb 2015 11:27:27 GMT

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.

1	A.	1.	Bio	100
2	A.	1.	Tab	50
3	A.	2.	Che	75
4	A.	2.	Bio	100
5	A.	2.	Lei	50
6	B.	1.	Bio	100
7	B.	1.	Tab	50
8	B.	2.	Che	75
9	B.	2.	Bio	100
10	B.	2.	Lei	50

1	A.	1.	Bio	100
2	A.	1.	Tab	50
3	A.	2.	Che	75
4	A.	2.	Bio	100
5	A.	2.	Lei	50
6	B.	1.	Bio	100
7	B.	1.	Tab	50
8	B.	2.	Che	75
9	B.	2.	Bio	100
10	B.	2.	Lei	50

1	A.	1.	Bio	100
2	A.	1.	Tab	50
3	A.	2.	Che	75
4	A.	2.	Bio	100
5	A.	2.	Lei	50
6	B.	1.	Bio	100
7	B.	1.	Tab	50
8	B.	2.	Che	75
9	B.	2.	Bio	100
10	B.	2.	Lei	50