How to present result using proc report?

[Phil_NZ]

Hi all SAS Users,

When using proc import to conduct the presentable table, following the code suggested by @Ksharp  and @ballardw , I have the code below

proc report data=present nowd;
columns  loc Market_Capitalization final_amh_ew final_vw_amh market_return_f market_vol no_of_stock no_of_year develop;
define develop/group noprint descending;
define loc/display;
define Market_Capitalization/analysis mean;
define final_amh_ew/analysis mean;
define final_vw_amh/analysis mean;
define market_return_f/analysis mean;
define market_vol /analysis mean;
define no_of_stock/analysis mean;
define no_of_year/analysis mean;
compute after develop;
loc='AVERAGE';
endcomp;
break after develop/summarize;
run;

And the result is like that

As can be seen from the Table, AVE is displayed rather than "Average". Can you please let me know how to display "Average" and/or insert one blank line under the first AVERAGE (for aesthetic purpose).

Please let me know if my description is not clear.

Warmest regards

[ballardw · Accepted solution]

Proc Import by default uses very few rows to "guess" properties for the variables. If the file is XLSX I think the number is 8, csv 20. So if the "longest" value in a character column in the first rows is three characters then everything later in the file is read as 3.

Run proc contents on your data set and verify the actual length and the format. There is also a chance that longer values may have had a format of $3. assigned, which will truncate existing values. Proc Report will use the default format. If this case, longer length with short assigned format, then you can override the format in Define statement of Proc Report to change the display. But if the values are short you need to reread your data.

If you created the LOC variable using code then you likely need to specify the LENGTH before use.

Please run this code to see a brief demonstration of one of the behaviors SAS has when you do not control your code.

data junk;
   x='ABC';output;
   x="A much longer string";output;
run;

Proc print data=junk;
run;

A character variable if a Length, Attribute, Format or Informat statement does not appear before the use / creation of the variable the length will be that of the first value assigned.

If you are importing a text file you really want to add the GUESSINGROWS=MAX; or at least a very large number, so SAS will use more rows to examine before guessing variable types and lengths.

With XLSX files, save to CSV and see the above note. You are the mercy of a Microsoft supplied engine. OR you can modify the registry to use more lines.

In a only loosely related item, the default format for your Market_capitalization variable appears to be best9. (?) You might improve readability by using something like Dollar18. or similar. To test that add : Format=Dollar18. after the / in the Define for that variable. Since you have a lot values that appear to be in the $283,300,000,000 range I am sure that everyone is going to worry about fractions of a dollar in the mean result. (Or you can check the Proc Format code for an example of creating a format that shows values like that as $283,300M or similar)

[Shmuel]

Try to change next line

define loc/display;

to:

define loc/display format=$8. width=10;

maybe format is enough and width not needed;