BookmarkSubscribeRSS Feed
Justin9
Obsidian | Level 7
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!

 

Justin9_0-1672262571666.png

 

4 REPLIES 4
PGStats
Opal | Level 21

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)

PG
Justin9
Obsidian | Level 7

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

Justin9
Obsidian | Level 7
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!
Patrick
Opal | Level 21

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.

SAS Innovate 2025: Call for Content

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!

Submit your idea!

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
  • 4 replies
  • 910 views
  • 0 likes
  • 3 in conversation