topic Re: How to calculate groupwise cumulative percentages in SAS Programming

How to calculate groupwise cumulative percentages

Shradha1 — Fri, 22 Jan 2021 13:24:16 GMT

I have a dataset containing country, state, city and total population within each:
Data popn;
Input country$ state$ city$ population;
Cards;
US California LA 100
US California SD 50
US Texas Houston 10
US Texas Austin 5
INDIA MH Mumbai 10
INDIA MH Pune 5
INDIA MH Nasik 7
INDIA WB Kol 6
INDIA UP Bs 4

What I require is state wise cumulative percentage of population, that is by the second column as groups:
US California LA 100 67%
US California SD 50. 100%
US Texas Houston 10 67%
US Texas Austin 5. 100%
INDIA MH Mumbai 10. 45%
INDIA MH Pune 5. 68%
INDIA MH Nasik 7. 100%
INDIA WB Kol 6. 100%
INDIA UP Bs 4. 100%

How do I get this ?

Re: How to calculate groupwise cumulative percentages

PeterClemmensen — Fri, 22 Jan 2021 13:39:36 GMT

Do you want a report or a SAS data set?

Data set:

data want(drop = p);
   p = 0;
   do until (last.state);
      set popn;
      by country state notsorted;
      p + population;
   end;

   do until (last.state);
      set popn;
      by country state notsorted;
      percent = divide(population, p);
      output;
   end;

   format percent percent8.;
run;

Result:

country  state     city     population  percent 
US       Californ  LA       100         67% 
US       Californ  SD       50          33% 
US       Texas     Houston  10          67% 
US       Texas     Austin   5           33% 
INDIA    MH        Mumbai   10          45% 
INDIA    MH        Pune     5           23% 
INDIA    MH        Nasik    7           32% 
INDIA    WB        Kol      6           100% 
INDIA    UP        Bs       4           100%

Re: How to calculate groupwise cumulative percentages

PaigeMiller — Fri, 22 Jan 2021 13:34:06 GMT

here's my code ... maybe someone has a shorter version

Data popn;
Input country $ state $ city $ population;
order=_n_;
Cards;
US California LA 100
US California SD 50
US Texas Houston 10
US Texas Austin 5
INDIA MH Mumbai 10
INDIA MH Pune 5
INDIA MH Nasik 7
INDIA WB Kol 6
INDIA UP Bs 4
;
proc summary data=popn nway;
    class country state;
    var population;
    output out=stats sum=pop_sum;
run;
proc sort data=popn;
    by country state;
run;
data want;
    merge popn stats;
    by country state;
run;
proc sort data=want;
    by order;
run;
data want2;
    set want;
    by country notsorted state notsorted;
    if first.state then cum_population=0;
    cum_population+population; /* Cumulative population */
    percent=cum_population / pop_sum;
run;

Re: How to calculate groupwise cumulative percentages

Shradha1 — Fri, 22 Jan 2021 13:48:29 GMT

I want sas dataset but cumulative percentages within each state and not percentages

Re: How to calculate groupwise cumulative percentages

PeterClemmensen — Fri, 22 Jan 2021 13:55:54 GMT

@Shradha1 Sorry, there you go

Data popn;
Input country$ state$ city$ population;
Cards;
US California LA 100
US California SD 50
US Texas Houston 10
US Texas Austin 5
INDIA MH Mumbai 10
INDIA MH Pune 5
INDIA MH Nasik 7
INDIA WB Kol 6
INDIA UP Bs 4
;

data want(drop = p);
   p = 0;
   do until (last.state);
      set popn;
      by country state notsorted;
      p + population;
   end;

   do until (last.state);
      set popn;
      by country state notsorted;
      percent = sum(percent, divide(population, p));
      output;
   end;

   format percent percent8.;
run;

Result:

country  state     city population  percent 
US       Californ  LA       100     67% 
US       Californ  SD       50      100% 
US       Texas     Houston  10      67% 
US       Texas     Austin   5       100% 
INDIA    MH        Mumbai   10      45% 
INDIA    MH        Pune     5       68% 
INDIA    MH        Nasik    7       100% 
INDIA    WB        Kol      6       100% 
INDIA    UP        Bs       4       100%