- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
proc sql;
create table Banded_1 as
select sc.score_band
,count(*) as Volume
,sum(sc.Projected12) as Projected
from ac_banded as sc
group by sc.score_band
order by sc.score_band;
quit;
data Banded_2;
set Banded_1;
if score_band in ('le550','le560) then score_band_new='le560';
else score_band_new=score_band;
run; proc sql; create table Banded_3 as select score_band ,sum(Volume) as Volume ,Projected from Banded_2 group by score_band;quit;
Hi, I've used the code above to try and calculate the overall 'Projected' figure for each score_band but when I try to combine the 'le550' and 'le560' score bands into one ('le560'), the summed 'Projected' figure in my 'Banded_3' output is incorrect as it gives me two 'le560' rows instead of just the one (as you can see with my screenshot below). Can someone help me to adjust my code to get my desired outcome, as shown below in the screenshot? Thanks in advance!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Should be done in a single step:
proc sql;
create table Banded as
select
case when score_band="le550" then "le560"
else score_band end as score_band_new,
count(*) as Volume,
sum(Projected12) as Projected
from ac_banded
group by calculated score_band_new;
quit;
(untested)
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for the reply. It's still not giving me the intended outcome that I had in the screenshot, so can someone suggest another method that I could use to edit the proc sql of the 'Banded_3' section of code? Ideally, I'd keep the first two sections of code as they are (though I did add in an extra line of code in 'Banded_1 with ,sum(sc.Projected12) as Projected
), as it was developed that way and I'm only trying to create 'Banded_3' to find out the sum of the 'Projected_12' for each of the score bands
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
If you're after some actual code that's tested then please provide suitable sample data in the form of a SAS data step that creates such data. Also show the desired result based on that sample data and explain the logic required to transform the source to the desired target state.