An alternative to proc means?

[SASuser4321]

I have the table app_port which looks like this:

snapshot_Date	final_slot	calc_client_slot	ead
31DEC2019	1	3	100
31DEC2017	2	2	120
31DEC2019	4	1	110
31DEC2017	1	1	140
31DEC2018	3	3	100

....

....

The table has 1000s of rows.

The output table that my current code outputs is the following:

observation_wise_override_rate	snapshot_date	flag__Mean	proportion_of_downgrades	proportion_of_upgrades
0.05	31DEC2017	0.03	92%	0.03
0.007	31DEC2018		91%	0.02
0.1	31DEC2019		93%	0.04

This output table however does not take the "date" variable in my app_port table into account for the flag__Mean column. Hence my flag__Mean column has missing values (I want to change this). This is because I didn't use proc sql for that (code below) and thus couldn't use 'group by'. I am hence looking for a proc sql equivalent of proc means or alternatively, a way in which I could keep proc means and still manage to group that column by dates.

This is my current code:

proc sql;
create Table observation_wise_override_rate AS
select sum(final_slot ne calc_client_slot) / count(*) as observation_wise_override_rate, snapshot_Date from app_port 
group by snapshot_Date;
quit;

proc sql;
create Table proportion_of_downgrades AS
select sum(final_slot > calc_client_slot) / sum(final_slot ne calc_client_slot) as proportion_of_downgrades from app_port
group by snapshot_Date;
quit;


proc sql;
create Table proportion_of_upgrades AS
select sum(final_slot < calc_client_slot) / sum(final_slot ne calc_client_slot) as proportion_of_upgrades from app_port
group by snapshot_Date;
quit;

proc sql;
create table _final_ as 
select final_slot, calc_client_slot,
case when
calc_client_slot = final_slot then 0    
else 1                  
end as flag_   
from app_port
group by snapshot_Date;
quit;


proc sql;
create table _app_port_ as
select * from app_port as x right join _final_ as y on x.final_slot=y.final_slot
group by snapshot_date;run;

ods select none;
proc means data= _app_port_ mean;
   var flag_;
   weight ead;
   ods output summary=exposure_wise_override_rate;
run;
ods select all;

data final;
merge observation_wise_override_rate exposure_wise_override_rate proportion_of_downgrades proportion_of_upgrades;
run;

Help would be appreciated. Thank you.

[SASuser4321 · Accepted solution]

This is what I tried right now and the code is able to run and shows me the table with no missing values. Do you think the following code does the same job as the proc means code shown in the OP?

proc sql;
create table exposure_wise_override_rate as
select sum(flag_*ead) / sum(ead) from _app_port_
group by snapshot_date;
quit;

[Shmuel]

Compare the sql output report  to 

proc means data= _app_port_ mean;
   class snapshot_date;   /* <== line added */
   var flag_;
   weight ead;
   ods output summary=exposure_wise_override_rate;
run;

[PaigeMiller]

Well, @SASuser4321 you may have marked your SQL as correct, but I disagree. If there are missing values in the data, your SQL will give the WRONG answer. On the other hand, if there are missing values in the data, then PROC MEANS will give the correct answer.