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!!!!
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;
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.
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;
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;
Thanks a lot 🙂 @novinosrin @ed_sas_member @ballardw
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.