topic Re: proc means creates unexpedted missing variables? in SAS Programming

proc means creates unexpedted missing variables?

Phil_NZ — Sun, 07 Mar 2021 02:29:38 GMT

Hi all SAS Users,

I have a dataset named "_w.trim_amihud", then I want to have the total annual market capital of all firms in one year in one country, I use the code

proc means data=_w.trim_amihud noprint;
     class LOC year;
     /*LOC is country*/
	 var ann_mar_cap;
     /*ann_mar_cap: annual market cap of each firm*/
	 output out=annual_mar_cap sum=sum_ann_mar_cap;
     /*sum_ann_mar_cap is total market cap of all firms in each year in one country. It is what I want to have*/
run;

I have two questions:

1. so whether the code above seems to be reasonable with what I want to have (total annual market capital of all firms in one year in one country)?

2. When I run the code, I saw the result as below (annual_mar_cap dataset)

LOC	year	_TYPE_	_FREQ_	sum_ann_mar_cap
	.	    0	    9231	1.3262869E14
	1999	1	    3377	4.5681573E13
	2000	1	    3004	4.6454925E13
	2001	1	    2850	4.0492192E13
ARG	.	    2	    36	    71670486162
AUS	.	    2	    305	    615750041684
AUT	.	    2	    71	    32876319884
/*continue with TYPE=2 for all countries*/
ARG	1999	3	    13	    32514376024
ARG	2000	3	    12	    24105289289
ARG	2001	3	    11	    15050820848
AUS	1999	3	    116	    224128296053
AUS	2000	3	    99	    193674631788

So, it seems to me that there are some missing observations in LOC or in year, quite surprise, so, I come back and check directly on the dataset "trim_amihud" but it is more surprise that there seems to be no missing observations regarding these two variables.

I also put the summary statistic of dataset annual_mar_cap here;

Could you please help me to sort it out, does the problem relate to _TYPE_ (because I saw the missing obs generated coming from TYPE=0 or 1?

Warmest regards.

Re: proc means creates unexpedted missing variables?

Tom — Sun, 07 Mar 2021 04:01:50 GMT

Read about the meaning of the _TYPE_ variable in the documentation.

Also look at the NWAY option on the PROC MEANS/SUMMARY statement.

Re: proc means creates unexpedted missing variables?

ballardw — Sun, 07 Mar 2021 05:59:22 GMT

By default Proc Means/Summary provides output statistics for every combination of class variable(s).

So if you have 2 class variables you get a summary for ALL values, _type_ = 0, each of the class variable levels alone, _type_ = 1 and 2, and then the combinations of the two class variables as _type_=3.

If you only want the last then the option NWAYS on the Proc statement would only have those.

There are a number of options for selecting which levels appear in the output , a WAYS statement, a TYPES statement, a CLASSDATA date set , COMPLETETYPES and some things that interact with these such as PRELOADFMT.

Believe me, this is a powerful feature. One pass through through the data set to create a bunch of different groups of statistics and then selecting the desired one(s) for a report based on _type_ can be very powerful.

I have one recurring project that reports stuff based on statewide, regions, counties, school districts, schools, school type and grades. Generating all of the summaries in one data set allows for very easy, relatively, report tables by selecting type.