Grouping?

[new_sas_user_4]

Hello everyone, 

I have the below dataset :

data have ;
length brand Region1 Region2 Region3 $4;
input brand Region1 Region2 Region3 Units;
datalines;
Audi ON BC ON 25
Audi BC ON AB 30
Audi AB BC ON 20
Audi ON ON BC 10

BMW ON BC ON 25
BMW BC ON ON 30
BMW AB BC BC 20
BMW ON BC AB 10
;

I want the output as :

I was thinking : select brand,region3,sum(units) as total_sales from have group by 1,2;

But this way... I will have to run thrice(region1/2/3) and then merge ...

Is there an efficient way to do ?

Thank you!!!!

[ballardw]

Do you expect a data set for further analysis or a report for people to read?

And describe the rule for Region (not currently a variable in your data set) and the Units variables. It may be obvious to you since it is your problem but not to others.

And did this format data come from summarizing something else? It might be easier to work with that source for some things.

[ed_sas_member]

Hi @new_sas_user_4 

Here is an attempt to do this

proc sort data=have out=have_sorted;
	by brand units;
run;

proc transpose data=have_sorted out=have_exp (rename= (col1=region)) name=regionID;
	var Region:;
	by brand Units;
run;

proc sort data=have_exp;
	by brand region;
run;

proc means data=have_exp noprint;
	var units;
	class brand region regionID;
	ways 3;
	output out=have_sum (drop = _type_ _freq_) sum=sum_units;
run;

proc transpose data=have_sum out=have_tr (drop=_name_) suffix=_units;
	var sum_units;
	id regionID;
	by brand region;
run;

[novinosrin · Accepted solution]

Hi @new_sas_user_4  what you need to think is restucture , sum and restructure again. That's the algorithm

data have ;
length brand Region1 Region2 Region3 $4;
input brand Region1 Region2 Region3 Units;
datalines;
Audi ON BC ON 25
Audi BC ON AB 30
Audi AB BC ON 20
Audi ON ON BC 10
BMW ON BC ON 25
BMW BC ON ON 30
BMW AB BC BC 20
BMW ON BC AB 10
;
/*Wide to Long*/
data temp;
 set have;
 array t region:;
 length vn region $32;
 do over t;
  vn=vname(t);
  Region=t;
  output;
 end;
 drop region1-region3; 
run;
/*Summarize*/
proc summary data=temp nway completetypes;
class brand region vn;
var units;
output out=temp2(drop=_:) sum=;
run;

/*Tranpose back to wide as you wanted it*/
proc transpose data=temp2 out=want(drop=_:);
by  brand region ;
id vn;
var units;
run;

[new_sas_user_4]

Thanks a lot 🙂 @novinosrin @ed_sas_member @ballardw