Hi @Lekhnath
I have updated the code to take into account this new requirement.
In my opinion, the easiest way to handle "overlapping" departments is to use a specific format .
In the below code, I have defined the format Dept. as a multilabel one (for example, 1 can be formatted either as "Tim" or "Chuck").
Then, you can invoke this format in the proc means and specify the option "MLF" to get summary statistics for each format category.
Best,
data have;
infile datalines dlm=" ";
input Year Month $ Office $ Sales_type $ Sales;
datalines;
2018 Jan Dallas A 10
2018 Jan Dallas B 13
2018 Jan Dallas C 15
2018 Jan Dallas D 20
2018 Jan NY A 5
2018 Jan NY B 9
2018 Jan NY C 7
2018 Jan NY D 17
2018 Jan DC A 15
2018 Jan DC B 19
2018 Jan DC C 17
2018 Jan DC D 19
2018 Feb Dallas A 11
2018 Feb Dallas B 14
2018 Feb Dallas C 16
2018 Feb Dallas D 21
2018 Feb NY A 6
2018 Feb NY B 10
2018 Feb NY C 8
2018 Feb NY D 18
2018 Feb DC A 16
2018 Feb DC B 20
2018 Feb DC C 18
2018 Feb DC D 20
;
run;
data have2;
set have;
if Office in ("Dallas","NY") and Sales_type in ("A","B") then Dept=1;
else if Office = "Dallas" and Sales_type in ("C","D") then Dept=2;
else if Office in ("NY", "DC") and Sales_type in ("C","D") then Dept=3;
else if Office="DC" and Sales_type in ("A","B") then Dept=4;
run;
proc sort data=have2 (drop= Office Sales_type);
by Year Month Dept;
run;
proc format fmtlib;
value Dept (multilabel)
1 = "Tim"
2 = "Sam"
3 = "Henry"
4 = "Rick"
1,4 = "Chuck";
run;
proc means data=have2 sum maxdec=0;
var Sales;
class Year Month Dept / mlf;
ways 3;
output out=want (drop=_type_ _freq_) sum=Sales;
format Dept Dept.;
run;
... View more
