topic Re: Grouping? in SAS Programming

Grouping?

new_sas_user_4 — Fri, 31 Jan 2020 15:30:01 GMT

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

Re: Grouping?

ballardw — Fri, 31 Jan 2020 16:03:55 GMT

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.

Re: Grouping?

ed_sas_member — Fri, 31 Jan 2020 16:08:26 GMT

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;

Re: Grouping?

novinosrin — Fri, 31 Jan 2020 16:19:08 GMT

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;

Re: Grouping?

new_sas_user_4 — Fri, 31 Jan 2020 20:22:03 GMT

Thanks a lot 🙂 @novinosrin @ed_sas_member @ballardw