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

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

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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;
PG

View solution in original post

4 REPLIES 4
art297
Opal | Level 21

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

 

CSU_KL
Fluorite | Level 6
Your codes work. Thank you for your help!

I would see the total so i removed the (drop=total) and get the output below.


| Obs | ID | Min | group | total |
| 1 | 1 | 4 | 1 | 24 |
| 2 | 1 | 5 | 1 | 24 |
| 3 | 1 | 1 | 1 | 24 |
| 4 | 1 | 2 | 1 | 24 |
| 5 | 1 | 3 | 1 | 24 |
| 6 | 1 | 4 | 1 | 24 |
| 7 | 1 | 2 | 1 | 24 |
| 8 | 1 | 3 | 1 | 24 |
| 9 | 1 | 2 | 1 | 14 |
| 10 | 1 | 3 | 1 | 14 |
| 11 | 1 | 4 | 1 | 14 |
| 12 | 1 | 2 | 1 | 14 |
| 13 | 1 | 3 | 1 | 14 |


PGStats
Opal | Level 21

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;
PG
CSU_KL
Fluorite | Level 6
Thank you! I like the codes.and get what i was looking for.
tot ID
38 1
0 2

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 4 replies
  • 1317 views
  • 2 likes
  • 3 in conversation