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!
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)
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.