Summation of observations

[75063]

Am trying to summarize data based on a condition. I would like to summarize some of the bikes together and the rest together.

The sample data is as below - 

data bike;
input bike $ sales;
datalines;
A       1
A       4
B       4
B       4
C01   5
C03   5
C04   5
;

Output desired - summation of sales of all C bikes and summation of rest of the bikes(A+B).  ie. "C" = (C01+C02+C03) and "rest" = (A+B)

bike sales
C 15
rest 13

Feel that the desired output can come from a union of two tables, one for all the C bikes and other for the rest of the bikes.

Something like - 

proc sql;
create table Bi as
select *
from DATA
where bike ^contains "C"
union all
select bike as "rest",
sum(sales) as sales
from (select * from DATA where bike contains "C" )
group by bike;
quit;

Looking for some guidance.

Thank you!

[novinosrin]

data bike;
input bike $ sales;
datalines;
A       1
A       4
B       4
B       4
C01   5
C03   5
C04   5
;

proc sql;
create table want as
select  ifC(first(bike)='C','C','REST') as brand,sum(sales) as sum_sales
from bike
group by brand;
quit;

[75063]

Thank you for your reply.  

I can see that the ifc(FIRST(  ) function takes the first character in a character string,  What if i want to choose the first two characters of the character string. 

For example if my data would have been  -

data bike;
input bike $ sales;
datalines;A       1
A       4
B       4
B       4
Ax01   5
Ax03   5
Ax04   5;

 then if I had to sum up all the data with Ax (Ax01+Ax02+Ax03)  and sum up the rest (A+B) with the desired output -

brand  sales 

Rest      13

Ax         15

Thank you!

[novinosrin · Accepted solution]

use substr(bike,1,2)='Ax'

Demo:

data bike;
input bike $ sales;
datalines;
A       1
A       4
B       4
B       4
Ax01   5
Ax03   5
Ax04   5
;

proc sql;
create table want as
select  ifC(upcase(substr(bike,1,2))='AX','AX','REST') as brand,sum(sales) as sum_sales
from bike
group by brand;
quit;

[75063]

Awesome 🙂 Thank you for the guidance.

[75063]

This worked very well for my problem 🙂 Thank you !

[andreas_lds]

As soon as you have more different groups proc sql gets unnecessary confusing. Why not using a format?

proc format;
   value $bikeGroups 
      'C01', 'C03', 'C04' = 'C'
      other = 'rest'
   ;
run;

proc summary data=bike nway;
   class bike / order=formatted;
   format bike $bikeGroups.;
   var sales;
   output out=work.want(drop=_type_ _freq_) sum=;
run;

[75063]

Thank you for your response and guidance.