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.
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
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;
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
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.
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
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.
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;
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.
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
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?
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
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
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.