Proc Means-PCT of each group from grand_total

[Ronein]

Hello

I am trying to merge two tables and the task is to spread out the value of the grand_total.

What is the best way to do it?

I know that I can add help field in each of tables (with value 1 for example) and then to merge them.

But I think there is a more clever way to do it.

Any advice?

 /************************************************************************/
  *--------------------------------------------------------------------*
          |Calculate PCT that each group contributes |
  *--------------------------------------------------------------------*
/************************************************************************/;

data RawData1;
input  region $  ID $  amount;
cards;
North 1 10
North 2 20
North 3 30
North 4 40
North 5 100
South 6 60
South 7 70
South 8 80
South 9 90
East 10 100
East 11 200
;
run;
proc means data=RawData1 noprint nway;
var amount;
class region;
output out=output1(keep=RegionTotal region)
               sum(amount)=RegionTotal;
Run;
proc means data=RawData1 noprint ;
var amount;
output out=output2(keep=Grand_Total)
               sum(amount)=Grand_Total;
Run;
Data output3;
Merge output1(in=a) output2(in=b);
pct=(RegionTotal/Grand_Total);
Format pct percent6.2  RegionTotal  Grand_Total dollar10.;
Run;

[Ronein]

I know to to do it by adding help variable.

Is there a shorter way ?

data RawData1;
input  region $  ID $  amount;
cards;
North 1 10
North 2 20
North 3 30
North 4 40
North 5 100
South 6 60
South 7 70
South 8 80
South 9 90
East 10 100
East 11 200
;
run;


proc means data=RawData1 noprint nway;
var amount;
class region;
output out=output1(keep=RegionTotal region)
               sum(amount)=RegionTotal;
Run;


proc means data=RawData1 noprint ;
var amount;
output out=output2(keep=Grand_Total)
               sum(amount)=Grand_Total;
Run;


Data output1;
set output1;
help=1;
Run;


Data output2;
set output2;
help=1;
Run;


Data output3;
Merge output1(in=a) output2(in=b);
by help;
pct=(RegionTotal/Grand_Total);
Format pct percent6.2  RegionTotal  Grand_Total dollar10.;
Run;
proc print data=output3 noobs;run;

[ChrisNZ]

Like this? 

proc summary data=RawData1 ;
  var amount;
  class region;
  output out=SUM sum=;
run; 

data output3;
  merge SUM(where=(_TYPE_=0) rename=(AMOUNT=GrandTotal )) 
        SUM(where=(_TYPE_=1) rename=(AMOUNT=RegionTotal));
  PCT=(RegionTotal/GrandTotal);
  format PCT percent6.2  RegionTotal  GrandTotal dollar10.;
run;

[Ronein]

Thanks.

If you run the code you can see that column "PCT"  has null  values (only value in first row is not null).

So actually we have not received the requested results.

[Kurt_Bremser]

There's no need for the merge:

data RawData1;
input  region $  ID $  amount;
cards;
North 1 10
North 2 20
North 3 30
North 4 40
North 5 100
South 6 60
South 7 70
South 8 80
South 9 90
East 10 100
East 11 200
;
run;

proc means data=RawData1 noprint nway;
var amount;
class region;
output
  out=output1 (keep=RegionTotal region)
  sum(amount)=RegionTotal
;
run;

proc sql noprint;
select sum(regiontotal) into :grandtotal
from output1;
quit;

data output3;
set output1;
grand_total = &grandtotal;
pct = (RegionTotal/Grand_Total);
format
  pct percent6.2
  RegionTotal Grand_Total dollar10.
;
run;

proc print data=output3 noobs;
run;

Result:

              Region        grand_
region         Total         total       pct

East            $300          $800      38% 
North           $200          $800      25% 
South           $300          $800      38% 

[Kurt_Bremser]

And the whole thing can be made even shorter:

data RawData1;
input region $ ID $ amount;
cards;
North 1 10
North 2 20
North 3 30
North 4 40
North 5 100
South 6 60
South 7 70
South 8 80
South 9 90
East 10 100
East 11 200
;
run;

proc sql noprint;
select sum(regiontotal) into :grandtotal
from output1;
quit;

data output;
set rawdata1;
by region notsorted;
retain region_total;
grand_total = &grandtotal;
format
  region_total grand_total dollar10.
  pct percent6.2
;
if first.region
then region_total = amount;
else region_total + amount;
if last.region
then do;
  pct = region_total / grand_total;
  output;
end;
keep region region_total grand_total pct;
run;

proc print data=output noobs;
run;

Result:

             region_        grand_
region         total         total       pct

North           $200          $800      25% 
South           $300          $800      38% 
East            $300          $800      38% 

[ChrisNZ]

Of course you are right. Sorry I typed this with access to SAS (and without my brain plugged).

This should work (hopefully mybrain is more awake now, still untested):

data output3;
  merge SUM(where=(_TYPE_=0) ) 
        SUM(where=(_TYPE_=1) rename=(AMOUNT=RegionTotal));
  if _N_ =1 then GrandTotal=Amount;
  retain GrandTotal;
  PCT=(RegionTotal/GrandTotal);
  format PCT percent6.2  RegionTotal  GrandTotal dollar10.;
run;

[Kurt_Bremser]

And the simplest solution is a SQL with a sub-select:

proc sql;
create table want as
select
  a.region,
  sum(a.amount) as region_total format=dollar10.,
  (select sum(b.amount) from rawdata1 b) as grand_total format=dollar10.,
  calculated region_total / calculated grand_total as pct format=percent6.2
from rawdata1 a
group by a.region;
quit;