topic Re: Summation of observations in SAS Programming

Summation of observations

75063 — Fri, 24 Aug 2018 02:24:34 GMT

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!

Re: Summation of observations

novinosrin — Fri, 24 Aug 2018 02:44:17 GMT

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;

Re: Summation of observations

75063 — Fri, 24 Aug 2018 03:37:40 GMT

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!

Re: Summation of observations

novinosrin — Fri, 24 Aug 2018 03:58:52 GMT

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;

Re: Summation of observations

andreas_lds — Fri, 24 Aug 2018 04:45:11 GMT

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;

Re: Summation of observations

75063 — Fri, 24 Aug 2018 06:21:48 GMT

Thank you for your response and guidance.

Re: Summation of observations

75063 — Fri, 24 Aug 2018 06:22:22 GMT

Awesome 🙂 Thank you for the guidance.

Re: Summation of observations

75063 — Fri, 24 Aug 2018 06:23:53 GMT

This worked very well for my problem 🙂 Thank you !