BookmarkSubscribeRSS Feed

Im trying to obtain a summary and provide a total for each variable by rep. The problem im having is that this code still brings back all records, however the average and and totals are correct but it displays the same numeric for each record. Any idea why it wont display 1 row with the totals?

PROC SQL;
create table work.redemptions_summary as
select
a.month_date,
repay_Type as Rep
count(*) as count,
avg (a.i_tv) as Ave_tv,
avg (a.bal_o) as Average_Bal,
avg (b.cii) as cii,
avg (b.deli) as del,
count(*) as red_vol,
sum(bal_out) as red_bal,
sum(case when Rep= 'Cap' then bal_out else 0 end) as cap,
sum(case when Rep= 'Int' then bal_out else 0 end) as int,
sum(case when Rep= 'Mixed' then bal_out else 0 end) as part,
sum(bal_out)as Total_Bal,
case
when remaining_months <= 1 then 'End'
when remaining_months <= 12 then 'OP'
when arr_stage_code ne 'ZZ' then 'Ar'
else 'Rem' end as Red_rea

from
work.red as a
left join work.red_stats as b
on a.anm = b.aco

where
platform = 'um'
and Arr_stage_code not in ('4','5','6')

group by
rep,

month_date;

quit;

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

I would imagine that its to do with the case statement creating a unique value for each of the rows.   You can try putting select distinct to start, and also remove the case statement.  You should then get one row per by group.

stat_sas
Ammonite | Level 13

Just bring case statement under group by with an aggregate function.

Keith0001
Calcite | Level 5

You need to aggregate your case statement.

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
  • 485 views
  • 0 likes
  • 4 in conversation