BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
75063
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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;

View solution in original post

7 REPLIES 7
novinosrin
Tourmaline | Level 20
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
Obsidian | Level 7

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
Tourmaline | Level 20

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
Obsidian | Level 7
Awesome 🙂 Thank you for the guidance.
75063
Obsidian | Level 7
This worked very well for my problem 🙂 Thank you !
andreas_lds
Jade | Level 19

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
Obsidian | Level 7
Thank you for your response and guidance.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 1841 views
  • 6 likes
  • 3 in conversation