topic proc sql create band and sum variable help in SAS Programming

proc sql create band and sum variable help

Justin9 — Wed, 28 Dec 2022 23:54:10 GMT

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!

Re: proc sql create band and sum variable help

PGStats — Wed, 28 Dec 2022 22:30:50 GMT

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)

Re: proc sql create band and sum variable help

Justin9 — Wed, 28 Dec 2022 23:55:54 GMT

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

Re: proc sql create band and sum variable help

Justin9 — Thu, 29 Dec 2022 00:20:02 GMT

If anyone has any ideas to change my added line in 'Banded_1' and the whole 'Banded_3' section (which is what I've created), please can you help me with the necessary code to get the screenshot please!

Re: proc sql create band and sum variable help

Patrick — Thu, 29 Dec 2022 01:37:51 GMT

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.