How to Print Observations that Come after a certain number of total units

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

How to Print Observations that Come after a certain number of total units

I am trying to figure out a way to print out all observations after a certain number of units has been reached. As an example:

Row      Member     Units     Date

1          A               4          1/1/2015

2          B               3          1/1/2015

3          A               3          1/5/2015    

4          A               3          1/6/2015

5          B               8          1/8/2015

6          A               6          1/11/2015    

For this example, I would like all rows/observations printed out once a unique member has reached over 10 units. Dates have been sorted because the interest is in how many observations and types of observations there are after 10 units has been reached. .

So the output should be:

Row      Member     Units     Date

1          A               6           1/11/2015

2          B               8           1/8/2015

Any help in figuring out how to determine the output would be appreciated!

As an aside, I am able to determine the total number of units and number of members > 10 units with:

proc sql ;

select member, sum(units) as totalunits ;

from data

group member

having totalunits > 10 ;

run ;


Accepted Solutions
Solution
‎04-16-2015 08:11 AM
Super User
Posts: 9,856

Re: How to Print Observations that Come after a certain number of total units


data have;
input Row      Member   $  Units     Date : $20.;
cards;
1          A               4          1/1/2015
2          B               3          1/1/2015
3          A               3          1/5/2015    
4          A               3          1/6/2015
5          B               8          1/8/2015
6          A               6          1/11/2015  
;
run;
data want;
 set have;
 if _n_ eq 1 then do;
  declare hash h();
  h.definekey('Member');
  h.definedata('Sum','found');
  h.definedone();
 end;
 sum=.;found=.;
 rc=h.find();
 sum+units;
 h.replace();
 if sum gt 10 and not found then do;found=1;h.replace();output;end;
 drop rc sum;
run;

Xia Keshan

View solution in original post


All Replies
Solution
‎04-16-2015 08:11 AM
Super User
Posts: 9,856

Re: How to Print Observations that Come after a certain number of total units


data have;
input Row      Member   $  Units     Date : $20.;
cards;
1          A               4          1/1/2015
2          B               3          1/1/2015
3          A               3          1/5/2015    
4          A               3          1/6/2015
5          B               8          1/8/2015
6          A               6          1/11/2015  
;
run;
data want;
 set have;
 if _n_ eq 1 then do;
  declare hash h();
  h.definekey('Member');
  h.definedata('Sum','found');
  h.definedone();
 end;
 sum=.;found=.;
 rc=h.find();
 sum+units;
 h.replace();
 if sum gt 10 and not found then do;found=1;h.replace();output;end;
 drop rc sum;
run;

Xia Keshan

Respected Advisor
Posts: 3,156

Re: How to Print Observations that Come after a certain number of total units

Obviously Xia's solution is the optimal one, you can also choose 1. a sort by member and date, then a data step. 2. a proc sql like the following:

data have;

     input Row      Member $  Units     Date : mmddyy10.;

     format Date : mmddyy10.;

     cards;

1          A               4          1/1/2015

2          B               3          1/1/2015

3          A               3          1/5/2015   

4          A               3          1/6/2015

5          B               8          1/8/2015

6          A               6          1/11/2015 

;

run;

proc sql;

     create table want as

           select *,(select sum(units) from have where member=a.member and date <=a.date) as sum_units

                from have a

                     where calculated sum_units >10;

quit;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 160 views
  • 1 like
  • 3 in conversation