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

Hi,

 

Below is a data set, I need to find the identifier, its count and payout greater than 45

 

identiferpayoutregion

1101
2202
3101
4203
5101
6202
7103
8203
9102
10204
11104
12202

 

I want output like:

regioncount identifiersum payout

 

2              4                              70

3              3                              50

 

How to group by in data step with aggregate functions using first. and last.

 

Thanks and Regards,

 

Ankita

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

Even simple things can be done in many different ways. My favourite when dealing with BY groups, with FIRST and LAST conditions available, is FIRST - All - LAST.

 

FIRST - All - LAST logic : 

If first of group then
    set initialize sum to 0
add to sum (running sum)
if last of group and sum is over 45
    output

PG

View solution in original post

3 REPLIES 3
Reeza
Super User

Can you please post what you've tried. You're correct in needing first/last and BY group processing but it sounds like a homework assignment and hopefully you've attempted it. 

 

Logic. 

 

If first of group then

    set sum to first value

if last of group

    increment running total

    check if total is over 45

    if over 45 output

if not first or last

    add to total (running total). 

 

 

 

 

 

 

PGStats
Opal | Level 21

Even simple things can be done in many different ways. My favourite when dealing with BY groups, with FIRST and LAST conditions available, is FIRST - All - LAST.

 

FIRST - All - LAST logic : 

If first of group then
    set initialize sum to 0
add to sum (running sum)
if last of group and sum is over 45
    output

PG
Ksharp
Super User

data have;
infile cards expandtabs truncover;
input obs a b ;
cards;
1	10	1
2	20	2
3	10	1
4	20	3
5	10	1
6	20	2
7	10	3
8	20	3
9	10	2
10	20	4
11	10	4
12	20	2
;
run;

proc sql;
create table want as 
 select b,count(*) as count,sum(a) as sum 
  from have 
   group by b 
    having calculated sum gt 45 ;
quit;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1566 views
  • 1 like
  • 4 in conversation