Hello all,
In the attached example dataset, I want to get total min by ID. But the difficult points have two: first if the group = 0 (in white), the min will not count to the total. Second, if sub-total min of consecutive observations < 10 (in gray) where group = 1, then min will not count to the total either. So basically, I need to the total min in green by ID.
Thank you very much in advance for your help!
Kaigang
To implement both conditions you need to calculate subtotals:
data have;
input ID Min group @@;
datalines;
1 2 1 1 3 1 1 4 1 1 5 0 1 2 0 1 3 0 1 4 1
1 5 1 1 1 1 1 2 1 1 3 1 1 4 1 1 2 1 1 3 1
1 1 0 1 1 0 1 1 0 1 2 1 1 3 1 1 4 1 1 2 1
1 3 1 1 2 0 1 1 1 1 2 1 1 1 1 2 1 1 2 2 1
2 1 1 2 2 0 2 2 0 2 10 0 2 1 0 2 1 1 2 1 1
2 1 1 2 1 1 2 2 1 2 3 0 2 4 0 2 2 0 2 3 0
2 3 0 2 1 1 2 3 1 2 1 1 2 1 1
;
proc print data=have; by id group notsorted; id id group; run;
data want;
tot = 0;
do until (last.ID);
stot = 0;
do until (last.group);
set have;
by ID group notsorted;
stot + min;
end;
if group ne 0 and not (group = 1 and stot < 10) then
tot + stot;
end;
output;
keep ID tot;
run;
proc print noobs; run;
Not sure if I correctly understand what you want your output to look like. Sounds like the following might be what you're looking for:
data want (drop=total); do until (last.group); set have; by id group notsorted; if group ne 0 then do; if first.group then total=min; else total+min; end; else total=0; end; do until (last.group); set have; by id group notsorted; if group ne 0 and total ge 10 then output; end; run;
Art, CEO, AnalystFinder.com
To implement both conditions you need to calculate subtotals:
data have;
input ID Min group @@;
datalines;
1 2 1 1 3 1 1 4 1 1 5 0 1 2 0 1 3 0 1 4 1
1 5 1 1 1 1 1 2 1 1 3 1 1 4 1 1 2 1 1 3 1
1 1 0 1 1 0 1 1 0 1 2 1 1 3 1 1 4 1 1 2 1
1 3 1 1 2 0 1 1 1 1 2 1 1 1 1 2 1 1 2 2 1
2 1 1 2 2 0 2 2 0 2 10 0 2 1 0 2 1 1 2 1 1
2 1 1 2 1 1 2 2 1 2 3 0 2 4 0 2 2 0 2 3 0
2 3 0 2 1 1 2 3 1 2 1 1 2 1 1
;
proc print data=have; by id group notsorted; id id group; run;
data want;
tot = 0;
do until (last.ID);
stot = 0;
do until (last.group);
set have;
by ID group notsorted;
stot + min;
end;
if group ne 0 and not (group = 1 and stot < 10) then
tot + stot;
end;
output;
keep ID tot;
run;
proc print noobs; 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!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.