topic Re: Create Table with number of observartions and mean in SAS Studio

Create Table with number of observartions and mean

jozuleta — Sun, 04 Nov 2018 21:23:53 GMT

Hi experts,

my data sample (shortcut) looks like this:

	Stock	Country	Analyst	Broker	Date	TP2P
1	BCE	CA	Marc	RBC	06/30/2015	0.24
2	BCE	CA	Marc	RBC	01/17/2017	0.32
3	ATCO	CA	Marc	RBC	05/23/2017	-0.15
4	Inditex	ES	Jorge	CAJA	01/16/2018	0.03
5	IBM	US	Jon	BNY	05/30/2018	0.09
6	IBM	US	Jon	BNY	10/25/2016	0.01
7	APPLE	US	Julia	BNY	07/21/2017	0.09
8	APPLE	US	Sarah	UBS	07/30/2018	0.10

I am trying to build a table that provides the number of STOCKS, ANALYSTS and BROKER,
and the means of key variables by country.
The resulting table should be like this:

Panel A:
Country	# Stocks	# Analysts	# Broker	# Obs.	Mean (TP2P)
CA	2	1	1	3	0.14
ES	1	1	1	1	0.03
US	2	3	2	4	0.07
TOTAL	5	5	4	8
MEAN	1.66	1.66	1.33	2.66	0.08

and another table (Panel B) with the following result:

Panel B:
Year	# Obs.	#Analysts
2015	1	1
2016	1	1
2017	3	2
2018	3	3
MEAN	2	1.75

Can anyone help?

Re: Create Table with number of observartions and mean

Reeza — Sun, 04 Nov 2018 23:00:12 GMT

Are you familiar with SQL? That’s the fastest option in this case, due to the multiple distinct counts you’d like.

Re: Create Table with number of observartions and mean

jozuleta — Sun, 04 Nov 2018 23:22:29 GMT

@Reezaa little bit. If you have a solution using SQL I would appreciate!

Re: Create Table with number of observartions and mean

SuryaKiran — Mon, 05 Nov 2018 01:20:34 GMT

Check this:

data have;
infile datalines dlm=',';
input Stock :$8. Country :$8. Analyst :$8. Broker :$8. Date :$8.TP2P;
datalines;
BCE,CA,Marc,RBC,06/30/2015,0.24
BCE,CA,Marc,RBC,01/17/2017,0.32
ATCO,CA,Marc,RBC,05/23/2017,-0.15
Inditex,ES,Jorge,CAJA,01/16/2018,0.03
IBM,US,Jon,BNY,05/30/2018,0.09
IBM,US,Jon,BNY,10/25/2016,0.01
APPLE,US,Julia,BNY,07/21/2017,0.09
APPLE,US,Sarah,UBS,07/30/2018,0.10
;
run;


proc sql;
create table pre_want as
select 	monotonic() as row_no,
		Country, 
		count(distinct stock) format=4.2 as Stock, 
		count(distinct Analyst) format=4.2 as Analyst,
		count(distinct Broker) format=4.2 as Broker,
		count(*) format=4.2 as Obs,
		mean(TP2P) format=4.2 as mean_TP2P
	from have
group by Country
;
quit;

proc sql;
create table want(drop=row_no) as 
select * from pre_want
union
select monotonic()+10 as row_number,
		'TOTAL' as Country,
		sum(Stock) format=4.2 as Stock,
		sum(Analyst) format=4.2 as Analyst,
		sum(Broker) format=4.2 as Broker,
		sum(Obs) format=4.2as Obs,
		. format=4.2 as mean_TP2P
	from pre_want
union
select monotonic()+100 as row_number,
		'Mean' as Country,
		mean(Stock) format=4.2 as Stock,
		mean(Analyst) format=4.2 as Analyst,
		mean(Broker) format=4.2 as Broker,
		mean(Obs) format=4.2 as Obs,
		mean(mean_TP2P) format=4.2 as mean_TP2P
	from pre_want
order by 1
;
quit;

Re: Create Table with number of observartions and mean

Reeza — Mon, 05 Nov 2018 01:38:36 GMT

Are the data for each state mutually exclusive? Can you assume that you can add up the # of analysts or would that need to be separate count distinct()?

Re: Create Table with number of observartions and mean

jozuleta — Mon, 05 Nov 2018 02:20:54 GMT

count distinct. The code from SuryaKiran works good. Thanks to you, too Reeza!

Re: Create Table with number of observartions and mean

Reeza — Mon, 05 Nov 2018 02:30:34 GMT

The solution does not do a count distinct for totals. So if you have overlaps, this will not be correct.

Re: Create Table with number of observartions and mean

jozuleta — Mon, 05 Nov 2018 03:12:43 GMT

Do you mind to reply your solution to see better the differences in the codes / data ? And, What about Panel B (by years). Any help you could provide? Thanks

Re: Create Table with number of observartions and mean

Reeza — Mon, 05 Nov 2018 03:26:52 GMT

You should be able to adapt the code already given. For years, change the GROUP to include YEAR not STATE. For totals hit the main table again but consider what would go into the GROUP statement. You already have all the pieces, just need to reorder them a bit.
Also, see what happens if you have more than 10 states or years.

If you're having issues post the code you've modified and we can let you know what you need to do.

Re: Create Table with number of observartions and mean

jozuleta — Fri, 09 Nov 2018 01:24:31 GMT

Hi Reeza,

I thought it would be that easy as you mentioned but unfortunately it doesn't work 😞
Here the code:

proc sql;
create table MyData.PRE_Table1 as
select 	monotonic() as row_no,
		year format=BESTw. length=8, 
		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 MyData.SAMP_ESTIMATOR
group by year 
;
quit;
proc sql;
create table MyData.Tabelle_1(drop=row_no) as 
select * from MyData.PRE_Table1
union
select monotonic()+10 as row_number,
		'MEAN' 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 MyData.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 MyData.PRE_Table1
order by 1
;
quit;

The first table is created without any problems. But then in the second proc I get this error:

ERROR: Column 2 from the first contributor of UNION is not the same type as its counterpart from the second.

I do not see exactly why it should not be the same type ?!

Could you provide some help again? 🙂

Here is a screenshot of the Table (PRE_Table1) having also the information about the variable "year"

Re: Create Table with number of observartions and mean

Reeza — Fri, 09 Nov 2018 01:32:13 GMT

Year is numeric in your first data set and Total is clearly not numeric, it's a character variable.

You can change Year to be a character value (Put(year, 4.)) or you can Mean/Total to be a number and use a format to display it as mean/total. Changing Year to be a character is easier. But...if you ever need to sort this for some reason, then the number method is easier though you do need to have a format. I usually just set the number to something arbitrarily large (100000 = Mean, 100001 = Total).

Re: Create Table with number of observartions and mean

jozuleta — Fri, 09 Nov 2018 11:15:45 GMT

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!

Re: Create Table with number of observartions and mean

SuryaKiran — Fri, 09 Nov 2018 13:24:25 GMT

YEAR() function argument to be numeric. Remove put() function inside year()

year (Put(year, 4.)) ---> year(year)

Perform the calculations on the right grouped columns

Re: Create Table with number of observartions and mean

jozuleta — Fri, 09 Nov 2018 14:29:46 GMT

Hi SuryaKiran,

Unfortunately it doesn't work. Now I have a new column called "_TEMA007" instead of "year". Furthermore, the years are all set now to "1995".

my code:

proc sql;
create table JORGE.PRE_Table2 as
select 	monotonic() as row_no,
		year(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(distinct COMPLOC) fortmat=8.0 as COMPLOC,
		count(*) format=8.0 as Obs,
		mean(TP2P) format=8.2 as mean_TP2P
	from JORGE.SAMP_ESTIMATOR
group by year
;
quit;

Even If I a run the whole code the problems is still there:

ERROR: Column 2 from the first contributor of UNION is not the same type as its counterpart from the second.

Do you have an idea why the means are different from my reference table ? They shouldn't.

The Total of Observations are the same but apparently the means are not?! (even when changing the format of the numers to eg. 8.8)

Re: Create Table with number of observartions and mean

SuryaKiran — Fri, 09 Nov 2018 15:43:59 GMT

Do you have year column in JORGE.SAMP_ESTIMATOR dataset as numeric? then use put(year,4.) as year

When your appending two tables using UNION then you need to have same data types for same columns. In your case my guess is your first query has year numeric and the next queries are character.

Re: Create Table with number of observartions and mean

Reeza — Fri, 09 Nov 2018 15:55:47 GMT

@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.

Re: Create Table with number of observartions and mean

jozuleta — Fri, 09 Nov 2018 16:55:04 GMT

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!

Re: Create Table with number of observartions and mean

Reeza — Fri, 09 Nov 2018 16:57:52 GMT

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

Re: Create Table with number of observartions and mean

jozuleta — Fri, 09 Nov 2018 17:23:50 GMT

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 ^^

Re: Create Table with number of observartions and mean

jozuleta — Fri, 09 Nov 2018 17:49:57 GMT

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 😄