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!
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;
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;
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!
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;
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;
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.