Create Table with number of observartions and mean

[Reeza]

---

@jozuleta wrote:

Sorry, I guess I do not know exactly how to change it in my code.

I changed it like this:

proc sql;
create table JORGE.PRE_Table1 as
select 	monotonic() as row_no,
		Put(year, 4.) as Year, 
		count(distinct RIC) format=8.0 as RIC, 
		count(distinct ANCODE) format=8.0 as ANCODE,
		count(distinct BROKERNAM) format=8.0 as BROKERNAM,
		count(*) format=8.0 as Obs,
		mean(TP2P) format=8.2 as mean_TP2P
	from JORGE.SAMP_ESTIMATOR
group by year
;
quit;
proc sql;
create table JORGE.Tabelle_1(drop=row_no) as 
select * from JORGE.PRE_Table1
union
select monotonic()+10 as row_number,
		'TOTAL' as year,
		sum(RIC) format=8.0 as RIC,
		sum(ANCODE) format=8.0 as ANCODE,
		sum(BROKERNAM) format=8.0 as BROKERNAM,
		sum(Obs) format=8.0 as Obs,
		. format=8.2 as mean_TP2P
	from JORGE.PRE_Table1
union
select monotonic()+100 as row_number,
		'MEAN' as year,
		mean(RIC) format=8.0 as RIC,
		mean(ANCODE) format=8.0 as ANCODE,
		mean(BROKERNAM) format=8.0 as BROKERNAM,
		mean(Obs) format=8.0 as Obs,
		mean(mean_TP2P) format=8.2 as mean_TP2P
	from JORGE.PRE_Table1

;
quit;

---

The means should be all the same as the mean of the Reference table: 0.2191538

Conclusion: There is something wrong with the whole code 😞

Why would you expect the means to be the same?  They should not be the same otherwise what's the point of having the different groupings. The overall means should be the same, but if you haven any missing values again, they may not be.

See the above modification and let us know.

[jozuleta]

Guys thank you very much. It worked.

 
@Reeza Yes, for the groups they should not be the same (as you said) BUT the OVERALL MEANS should be the same (as you said, too). But unfortunately they are not the same.

If a run a proc means for all the three tables the means are different as described below. See screenshot

Why is this the reason and how can I solve it? I can not report tables with different overall means 😄

First proc mean is from the Reference Sample, second is from the Table by countries, third is from the Table by years.

Thanks for helping!

[Reeza]

Look at your N's, why aren't they the same?

[jozuleta]

They are not the same because one table is ordered by country (with N=63) and the other by years (with 18). But the overall mean should anyway be the same ?! I checked the overall observations in each table and they are for all the same (all the Tables consider 1574783 obs). I am starting to think that I am getting stupid ^^

[jozuleta]

I am definitely getting stupid and should do a break now 😄 Sorry for asking... of course they are not the same... the mean of means is different.... sorry...hahahah 😄

[Reeza]

Yes it is :), but if you want the same number, keep the counts and use that as weights to get the same number from your different calculations.