How do I create a table with percentages within a subgroup on my PROC SQL?-CodePudding

I have a table that displays contracts and a flag indicating the clients situation, a little like below:

Contract# | MOB | FLAG |
111111111 | 1   | 1
222222222 | 1   | 0
333333333 | 1   | 1
444444444 | 1   | 1
555555555 | 2   | 1
666666666 | 2   | 0

I would like to display the MOB | FLAG | % within the MOB

So far I have

PROC SQL;
SELECT 
MOB,
FLAG,
COUNT(*) AS Contracts,
((COUNT(*)*1.0)/(SELECT COUNT(*) FROM Clients)) as Share format percent8.2
FROM Clients
GROUP BY MOB,FLAG_LDP;

However my percentages are being calculated against the overall total (in this example, 6 contracts, not the 4 contacts with MOB 1 and 2 with MOB 2).

I understand this is a simple solution, but I'm currently out of ideas.

CodePudding user response：

Assuming you expect the percentage of each mob, flag within mob

data have;
    infile datalines4 delimiter="|";
    input contract mob flag;
    datalines4;
111111111 | 1   | 1
222222222 | 1   | 0
333333333 | 1   | 1
444444444 | 1   | 1
555555555 | 2   | 1
666666666 | 2   | 0
;;;;

proc sql;
    create table want as select t1.mob, t1.flag, t1.cnt/t2.sum_mob as share format percent8.2
        from (select mob, flag, count(*) as cnt from have group by mob, flag) t1 
        inner join 
        (select mob, count(*) as sum_mob from have group by mob) t2 
        on t1.mob=t2.mob;
quit;

CodePudding user response：

Different approaches:

Sum using conditions if they're 0/1

proc sql;
create table want as
select mob, Count(*) as N_Records, sum(flag=0) as Flag0, sum(flag=1) as flag1, mean(flag) as flag_pct format=percent12.1
from have
group by mob;
quit;

PROC FREQ - the OUTPCT option outputs different

proc freq data=have noprint;
table mob*flag / out=want outpct missing;
run;

CodePudding user response：

You almost had it, unfortunately you need to do 2 summaries and join them together, as you are counting by 2 different groupings:

PROC SQL;
    SELECT 
    a.MOB,
    a.FLAG,
    COUNT(*) AS Contracts,
    COUNT(*)/b.MOB_Share AS MOB_Share_pct format=percent9.1
    FROM have a
    LEFT JOIN ( SELECT 
                MOB,
                COUNT(*) AS MOB_Share
                FROM have
                GROUP BY MOB) b
    ON a.MOB = b.MOB
    GROUP BY a.MOB,a.FLAG;
RUN;