Customize ODS OUTPUT to create the Statistics table from a specific procedure

[Emma_at_SAS]

I want to use ODS OUTPUT to save the STATISTICS table from my PROC SURVEYMEAN procedure. However, the OUTPUT data contains different values than the STATISTICS table that I want to save. In this example, I want  clm but I get only the UpperCLMean. Thanks!

• ods trace on; proc surveymeans data=sashelp.cars mean clm nobs plots = none; var Horsepower; run; ods trace off; ods output Statistics=Output; proc surveymeans data=sashelp.cars mean clm nobs plots = none; var Horsepower; run; proc print data=output; run;

Output Added:
-------------
Name: Summary
Label: Data Summary
Template: Stat.SurveyMeans.Factoid
Path: Surveymeans.Summary
-------------

Output Added:
-------------
Name: Statistics
Label: Statistics
Template: Stat.SurveyMeans.Statistics
Path: Surveymeans.Statistics
-------------

The SAS System

The SURVEYMEANS Procedure

Data Summary
Number of Observations	428

Statistics
Variable	N	Mean	Std Error of Mean	95% CL for Mean
Horsepower	428	215.89	3.5	209.06	222.71

---

The SAS System

Obs	VarName	Mean	LowerCLMean	N	StdErr	UpperCLMean
1	Horsepower	215.89	209.06	428	3.5	222.71

[Emma_at_SAS]

I noticed the columns are all there, and I need to change the order for the columns!

Thanks!

[FreelanceReinh]

Hello @Emma_at_SAS,

You can use the RETAIN statement to rearrange the variables. For example, to obtain the order VarName N Mean StdErr LowerCLMean UpperCLMean, use:

data output(label='Statistics');
retain VarName N Mean StdErr;
set output;
run;

If you don't need to maintain the original dataset label, just omit the LABEL= dataset option as shown in the step below.

Alternatively, you can use multiple SET statements in conjunction with selective KEEP= or DROP= dataset options:

data output;
set output(keep=v: n);
set output(keep=m: s:);
set output;
run;

Then you don't need to write out the variable names.

[Emma_at_SAS]

Thank you, FreelanceReinhard!

The RETAIN statement works very well! I wonder how I may remove the OBS column? I want to use this data in a report and I am creating many tables for different variables. I do not want to remove the OBS column manually but OBS is not a real column and I cannot DROP it. Any suggestions would be appreciated!

Also, how may I add a column for the name of the variable in my SAS dataset. For example, age is called age_in_years. How may I add a column called var_name and the observation for that would be age_in_years.

Thanks

Obs	VarName	VarLabel	N	Mean	AGE_StdDev	LowerCLMean	UpperCLMean
1	AGE	AGE: How old are you today?	2400	15.12	3.57	2.61	4.18

[Tom]

Add the NOOBS option to the PROC PRINT statement.

You could also just add a VAR statement to the PROC PRINT step to change the order they are printed without having to add a step to re-order the variables in the dataset.

[Emma_at_SAS]

how may I add a column for the name of the variable in my SAS dataset. For example, age is called age_in_years. How may I add a column called sas_var_name and the observation for that would be age_in_years.

Thanks

[Emma_at_SAS]

Thank you, Tom!

[FreelanceReinh]

---

@Emma_at_SAS wrote:

Thank you, FreelanceReinhard!

The RETAIN statement works very well! I wonder how I may remove the OBS column? I want to use this data in a report and I am creating many tables for different variables. I do not want to remove the OBS column manually but OBS is not a real column and I cannot DROP it. Any suggestions would be appreciated!

Also, how may I add a column for the name of the variable in my SAS dataset. For example, age is called age_in_years. How may I add a column called var_name and the observation for that would be age_in_years.

---

You're welcome.

Where would this (other) "name of the variable" come from? Normally such longer descriptions of a variable are stored in its label, which is included as variable VarLabel in the ODS output dataset, as shown in your output and in the example below (using the NOOBS option and the VAR statement of PROC PRINT as suggested by Tom).

ods output Statistics=Output;
proc surveymeans data=sashelp.cars mean clm nobs plots = none;
var EngineSize; 
run;

proc print data=output noobs;
var v: n m: s: l: u:;
run;

PROC PRINT output:

                                                                                      Lower         Upper
 VarName         VarLabel                   N            Mean          StdErr        CLMean        CLMean

EngineSize    Engine Size (L)             428        3.196729        0.053586    3.09140391    3.30205404

[ballardw]

---

@Emma_at_SAS wrote:

Thank you, FreelanceReinhard!

The RETAIN statement works very well! I wonder how I may remove the OBS column? I want to use this data in a report and I am creating many tables for different variables.

---

What kind of "report" are you creating? All of the typical SAS report procedures, Print, Report and Tabulate allow you to control which variables appear at all, labels and lots of other things. So just don't include OBS in the syntax.

Or if you are being lazy for code you can tell any specific procedure to ignore specific variables for that step using a data set option:

Proc print data=sashelp.class (drop=Age);

run;

for example. Uses the default Print behavior of printing "everything" but since the variable has been dropped from the Procedure the Age does not appear. The data set is not modified permanently though.

[Emma_at_SAS]

Thank you, Tom, for your explanation!

I have a large data set with a mix of continuous and categorical variables. For the first round of conversation among our team, I am preparing summary tables for our continuous and categorical variables. I copied the example tables below for the age of the participants as AGE_IN_YEARS and the age category of parents as AGE_GROUPS_PARENTS. Among the options you mentioned, I am using proc print. Basically, I save the output results in the output datasets:

ods output Summary=Output; for the continuous variables

ods output OneWay=Output; for the categorical variables

Then, use PROC PRINT to print the Output dataset in a Word file.

I am working to make a MACRO to avoid repetition. I would love to know if there is an easier way to do this.

Age [AGE_IN_YEARS] How old are you today? (years)
Mean                       12.25
Standard Deviation 1.17
95% CI                     13.61%, 14.18%
N                              2500

Age category of parents [AGE_GROUPS_PARENTS] Age in categories. (years)

                         Frequency  Percent        95% CI

16-25 years      515            20.8%           18.2%, 23.4%
26-35 years      526            21.3%           19.1%, 23.4%
36-45 years      453            18.3%           16.5%, 20.1%
46-55 years      491            19.8%           18.0%, 21.7%
56-65 years      490            19.8%           18.1%, 21.5%

[Emma_at_SAS]

ballardw, In this post to Tom I meant you! Sorry, about that! I do not see an option to edit that in my post!

Thanks!