Hi, Depending on my undestanding on your need, here is the code. This code can be made even simpler when making it as a macro, due to time constraint I didn't recode it into a macro. You can try by understanding my code. DATA one;
infile datalines dlm=",";
input Drink :$20. Type $ Dairy $ Price;
datalines;
Mocha,Coffee,Yes,4
Americano,Coffee,No,3
Cappuccino,Coffee,Yes,4
Solo,Coffee,No,1
Doppio,Coffee,No,2
Earl Grey Latte,Tea,Yes,4
London Fog,Tea,Yes,4
Iced Green Tea,Tea,No,2
Iced Sweet Tea,Tea,No,3
Chocolate Milkshake,Blended,Yes,5
Vanilla Milkshake,Blended,Yes,5
Strawberry Milkshake,Blended,Yes,5
Mango Smoothie,Blended,No,4
Strawberry Smoothie,Blended,No,3
Banana Smoothie,Blended,No,3
;
run;
Data one;
Set one;
run;
proc sort data=one;
by type price ;
run;
DATA One_(Keep=Total price_3 type);
retain Total price_3;
set one;
by type;
If first.type then do;
Total=1;
price_3=0;
end;
else Total+1;
if price>=3 then price_3+1;
If last.type then output;
Label Total="Total Drinks" price_3="Price >=3";
run;
proc transpose data=One_ Out=need_1(drop=_Name_) Label=Label;
ID type;
run;
DATA need1(drop=coffee_ Tea_ Blended_);
FORMAT Label $40. coffee $8. Tea $8. Blended $8. Combined $8.;
set need_1(rename=(coffee=coffee_ Tea=Tea_ Blended=Blended_));
IF _N_=1 then DO;
COFFEE=CAT(coffee_,"(100)");
Tea=CAT(Tea_,"(100)");
Blended=CAT(Blended_,"(100)");
combined=CAT(SUM(Blended_,tea_,coffee_),"(100)");
CALL SYMPUT("Coffee_Total",Coffee_);
CALL SYMPUT("Tea_Total",Tea_);
CALL SYMPUT("Blended_Total",Blended_);
CALL SYMPUT("Combined_Total",SUM(Blended_,tea_,coffee_));
END;
ELSE DO;
Coffee=CAT(Coffee_,'(',(Coffee_*100/SYMGET("Coffee_Total")),')');
Tea= CAT(Tea_,'(',(Tea_*100/SYMGET("Tea_Total")),')');
Blended=CAT(Blended_,'(',(Blended_*100/SYMGET("Blended_Total")),')');
Combined=CAT(SUM(Blended_,tea_,coffee_),'(',SUM(Blended_,tea_,coffee_)*100/SYMGET("Combined_Total"),')');
END;
RUN;
data two;
set one;
IF Dairy="No";
RUN;
proc sort data=two;
by type price ;
run;
DATA Two_(Keep=Total price_3 type);
retain Total price_3;
set two;
by type;
If first.type then do;
Total=1;
price_3=0;
end;
else Total+1;
if price>=3 then price_3+1;
If last.type then output;
Label Total="Drinks Without Dairy" price_3="Price >=3";
run;
proc transpose data=Two_ Out=need_2 (drop=_Name_) Label=Label;
ID type;
run;
DATA need2(drop=coffee_ Tea_ Blended_);
FORMAT Label $40. coffee $8. Tea $8. Blended $8. Combined $8.;
set need_2(rename=(coffee=coffee_ Tea=Tea_ Blended=Blended_));
Coffee=CAT(Coffee_,'(',(Coffee_*100/SYMGET("Coffee_Total")),')');
Tea= CAT(Tea_,'(',(Tea_*100/SYMGET("Tea_Total")),')');
Blended=CAT(Blended_,'(',(Blended_*100/SYMGET("Blended_Total")),')');
Combined=CAT(SUM(Blended_,tea_,coffee_),'(',ROUND(SUM(Blended_,tea_,coffee_)*100/SYMGET("Combined_Total"),0.11),')');
RUN;
data Three;
set one;
IF Dairy="Yes";
RUN;
proc sort data=Three;
by type price ;
run;
DATA Three_(Keep=Total price_3 type);
retain Total price_3;
set Three;
by type;
If first.type then do;
Total=1;
price_3=0;
end;
else Total+1;
if price>=3 then price_3+1;
If last.type then output;
Label Total="Drinks With Dairy" price_3="Price >=3";
run;
proc transpose data=Three_ Out=need_3 (drop=_Name_) Label=Label;
ID type;
run;
DATA need3(drop=coffee_ Tea_ Blended_);
FORMAT Label $40. coffee $8. Tea $8. Blended $8. Combined $8.;
set need_3(rename=(coffee=coffee_ Tea=Tea_ Blended=Blended_));
Coffee=CAT(Coffee_,'(',(Coffee_*100/SYMGET("Coffee_Total")),')');
Tea= CAT(Tea_,'(',(Tea_*100/SYMGET("Tea_Total")),')');
Blended=CAT(Blended_,'(',(Blended_*100/SYMGET("Blended_Total")),')');
Combined=CAT(SUM(Blended_,tea_,coffee_),'(',ROUND(SUM(Blended_,tea_,coffee_)*100/SYMGET("Combined_Total"),0.11),')');
RUN;
Data Final;
SET need1 need2 need3;
run;
... View more