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,
year (Put(year, 4.)),
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
order by 1
;
quit; ...and I get now the following error: ERROR: Function YEAR requires a numeric expression as argument 1. Furthermore, I checked the means between the different tables (Reference table vs table ordered by country vs table ordered by year) for the TP2P variable.They are all different...which means that there is something wrong with the first code. - Reference table (SAMP_ESTIMATOR): mean of 0.2191538 (using proc means) - Table ordered by country (Pre_Table1): 0.1918582 (using proc means) - Table ordered by year (Pre_Table2): 0.2271409 (using proc means) 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 😞 I will provide you with the two tables, maybe it helps to solve the problem easier (the reference table is too large to upload). - Pre_Table1 is ordered by country - Pre_Table2 is ordered by year Thanks!
... View more
